Window functions — ranking, LAG/LEAD, running totals and moving averages

Aggregates that do not collapse rows

GROUP BY collapses rows into one per group. A window function computes an aggregate or ranking across a set of related rows while keeping every row. Syntax:

function(...) OVER (
  PARTITION BY ...   -- split into independent groups (optional)
  ORDER BY ...       -- order within each group (needed for ranking, LAG, running totals)
  ROWS BETWEEN ...   -- frame: which rows around the current one (optional)
)

Start with a small monthly summary we will reuse. Each store's monthly net revenue in 2025, plus the store's annual total next to every month:

WITH monthly AS (
  SELECT o.store_id, strftime('%Y-%m', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
  GROUP BY 1, 2
)
SELECT store_id, month, ROUND(revenue) AS revenue,
       ROUND(SUM(revenue) OVER (PARTITION BY store_id)) AS store_year_total,
       ROUND(100 * revenue / SUM(revenue) OVER (PARTITION BY store_id), 1) AS pct_of_year
FROM monthly
ORDER BY store_id, month
LIMIT 6;

SUM(revenue) OVER (PARTITION BY store_id) repeats the store's annual total on each of its rows, so each month can be expressed as a share of the year — impossible with plain GROUP BY in one step.

ROW_NUMBER, RANK and DENSE_RANK

All three number rows in a defined order; they differ only on ties:

• ROW_NUMBER — always unique (ties broken arbitrarily unless you add a tie-breaker).
• RANK — ties share a rank, then gaps.
• DENSE_RANK — ties share a rank, no gaps.

Top N per group

The top 3 products by 2025 revenue within each category:

WITH product_rev AS (
  SELECT p.category, p.product_name,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p     ON p.product_id = oi.product_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_rev
)
SELECT category, rn, product_name, ROUND(revenue) AS revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn
LIMIT 9;

Window functions are computed after WHERE, so you cannot filter on rn in the same query level — wrap it in a CTE and filter outside.

Latest record per entity

Each customer's most recent completed order — a pattern used daily (latest status, latest price, last contact):

WITH ordered AS (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders WHERE status = 'completed'
)
SELECT customer_id, order_id AS last_order_id, order_date AS last_order_date
FROM ordered WHERE rn = 1
ORDER BY customer_id
LIMIT 5;

The tie-breaker order_id DESC makes the result deterministic when a customer ordered twice on the same day.

LAG and LEAD: comparing with previous and next rows

LAG(x, n) returns the value of x from n rows before the current row within the window; LEAD looks ahead. Month-over-month growth for total revenue:

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
  GROUP BY 1
)
SELECT month,
       ROUND(revenue) AS revenue,
       ROUND(LAG(revenue) OVER (ORDER BY month)) AS prev_month,
       ROUND(100 * (revenue / LAG(revenue) OVER (ORDER BY month) - 1), 1) AS mom_growth_pct
FROM monthly
ORDER BY month;

January has no previous month in this window, so LAG returns NULL — correctly, rather than inventing 0. December's jump and January's fall are seasonal; month-over-month comparisons can mislead in seasonal businesses, which is why the next lesson builds year-over-year comparisons with LAG(revenue, 12).

Running totals

A running (cumulative) total sums all rows from the start of the window to the current row:

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
  GROUP BY 1
)
SELECT month, ROUND(revenue) AS revenue,
       ROUND(SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS ytd_revenue
FROM monthly;

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the frame. If you omit it but include ORDER BY, the default frame is RANGE … CURRENT ROW, which treats rows with equal ORDER BY values as one — usually fine for unique months, surprising for duplicate dates. State the frame explicitly.

Moving averages

A 3-month moving average smooths noise:

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct)) AS revenue
  FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed' AND o.order_date >= '2025-01-01'
  GROUP BY 1
)
SELECT month, ROUND(revenue) AS revenue,
       ROUND(AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS ma_3m,
       COUNT(*) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS months_in_avg
FROM monthly;

Practice

Summary

• OVER (PARTITION BY … ORDER BY … ROWS …) computes across related rows without collapsing them.
• ROW_NUMBER / RANK / DENSE_RANK differ on ties; use them for top-N-per-group and latest-record queries (filter in an outer query).
• LAG/LEAD compare with previous/next rows; NULL at the edges is correct.
• Running totals and moving averages use frames; state frames explicitly and fill time gaps with a calendar.