Window Functions: Ranking, LAG/LEAD, Running Totals

In one line

Window functions compute a value for each row from a set of related rows (the window) without collapsing them like GROUP BY. ROW_NUMBER, RANK and DENSE_RANK rank rows; LAG/LEAD read neighbours; SUM ... OVER (ORDER BY ...) gives running totals and moving averages; conditional aggregation with FILTER pivots data.

0/6 · 0%

Think of it like this

A race results sheet. Each runner keeps their own row, but you can also write their position (rank), the gap to the runner ahead (lag), and the cumulative distance run so far (running total) next to it.

Key ideas

01
OVER (PARTITION BY x ORDER BY y): PARTITION splits rows into independent groups; ORDER defines order within each; the frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) limits which rows the function sees.
02
Ties: ROW_NUMBER gives 1,2,3,4 (arbitrary among ties), RANK gives 1,2,2,4 (gaps), DENSE_RANK gives 1,2,2,3 (no gaps).
03
Top-N per group: ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) in a subquery, then WHERE rn <= 3. Window functions can't go in WHERE directly because they're computed after it.
04
LAST_VALUE surprise: the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
05
Conditional aggregation: count(*) FILTER (WHERE status = 'paid') or sum(CASE WHEN ... THEN amount END) turns rows into columns in one pass.

Code & diagrams

windows.sqlsql

-- top 3 products per category by revenue
SELECT * FROM (
  SELECT category_id, product_id, revenue,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn
  FROM product_revenue
) t WHERE rn <= 3;

-- daily revenue with running total, 7-day moving average and day-over-day change
SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day)                                   AS running_total,
       avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d,
       revenue - LAG(revenue) OVER (ORDER BY day)                         AS change
FROM daily_sales_total ORDER BY day;

-- pivot order statuses per merchant
SELECT merchant_id,
       count(*) FILTER (WHERE status = 'paid')      AS paid,
       count(*) FILTER (WHERE status = 'refunded')  AS refunded,
       round(100.0 * count(*) FILTER (WHERE status = 'refunded') / count(*), 2) AS refund_pct
FROM orders GROUP BY merchant_id;

Interview problem

The problem

Find user sessions from click events

Events are (user_id, ts). A session ends after 30 minutes of inactivity. Assign a session number to each event and compute session durations, in SQL.

When it breaks

ROW_NUMBER for pagination or dedup with a non-unique ORDER BY

What you see

Ties are ordered arbitrarily, so repeated runs pick different "first" rows and dedup jobs delete different duplicates each time.

Fix & prevent

Add a unique tiebreaker to the ORDER BY (e.g. ORDER BY created_at DESC, id DESC).

Explain it without notes

What is the difference between RANK and DENSE_RANK?

Practice

Return the second-highest salary per department, handling ties.

Trade-offs

↔
Window functions replace self-joins and correlated subqueries, but large partitions still need sorts and memory (work_mem).

Done when you can

I can use ranking, offset and frame-based window functions, including top-N per group.

Command Palette