Lesson 4 of 8 · 50 min

Window functions & framing

ROW_NUMBER/RANK, LAG/LEAD, running totals, ROWS vs RANGE traps, QUALIFY, sessionization sketch, top-N per group.

Lesson 4 · Windows

Ranking, running metrics, and framing (the senior differentiator)

Windows are where mid-level SQL answers stall

Interviewers use windows for 'first order per user', running revenue, sessionization helpers, and top-N per group without collapsing rows. The hard part is not ROW_NUMBER syntax - it is PARTITION BY grain, ORDER BY tie-breaks, and frame clauses (ROWS vs RANGE) that silently change totals. Master the model; do not memorize one blog query.

A window function computes across a related row set without collapsing the result grain. GROUP BY reduces rows; windows decorate rows. Mix them carefully: often aggregate in a CTE, then window; or window first, then filter with QUALIFY/subquery.

Anatomy: OVER (PARTITION BY ... ORDER BY ... frame)

sql

1fn(...) OVER (2  PARTITION BY group_key      -- optional: reset peers3  ORDER BY sort_key           -- optional for some fns; required for rank/lag4  ROWS BETWEEN ... AND ...    -- optional frame; defaults matter!5)67-- Mental model:8-- 1) split rows into partitions9-- 2) order inside each partition10-- 3) apply frame (which neighbors visible)11-- 4) compute fn for current row

Default frames: If you ORDER BY without an explicit frame, many engines use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for aggregates like SUM - a running total by peer groups of the ORDER BY key, not always 'physical previous rows'. When in doubt for row-wise running totals, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Ranking family: ROW_NUMBER, RANK, DENSE_RANK, NTILE

ROW_NUMBER: unique sequence, arbitrary among ties unless you add tie-breakers. RANK: ties share rank, gaps after. DENSE_RANK: ties share, no gaps. NTILE(k): bucket into k roughly equal groups. For 'top 1 per group', ROW_NUMBER + filter is king.

sql

1-- Latest order per user2WITH ranked AS (3  SELECT4    o.*,5    ROW_NUMBER() OVER (6      PARTITION BY user_id7      ORDER BY created_at DESC, order_id DESC  -- deterministic ties8    ) AS rn9  FROM orders o10)11SELECT * FROM ranked WHERE rn = 1;1213-- Top 3 products per category by revenue14SELECT *15FROM (16  SELECT17    category_id,18    product_id,19    revenue,20    DENSE_RANK() OVER (21      PARTITION BY category_id ORDER BY revenue DESC22    ) AS rnk23  FROM product_revenue24) t25WHERE rnk <= 3;

LAG / LEAD and delta metrics

sql

1SELECT2  user_id,3  order_id,4  created_at,5  amount,6  LAG(created_at) OVER (7    PARTITION BY user_id ORDER BY created_at, order_id8  ) AS prev_at,9  amount - LAG(amount) OVER (10    PARTITION BY user_id ORDER BY created_at, order_id11  ) AS amount_delta12FROM orders;1314-- Days since previous order15-- created_at::date - LAG(created_at::date) OVER (...)

Sessionization lite: new session if minutes since previous event > 30. Use LAG timestamps, flag breaks, then SUM flags as session_id. Full sessionization can be deep; show the pattern and name edge cases (out-of-order events, clock skew).

sql

1WITH ordered AS (2  SELECT3    user_id,4    event_id,5    ts,6    LAG(ts) OVER (PARTITION BY user_id ORDER BY ts, event_id) AS prev_ts7  FROM events8),9flagged AS (10  SELECT *,11    CASE12      WHEN prev_ts IS NULL THEN 113      WHEN EXTRACT(EPOCH FROM (ts - prev_ts)) > 30*60 THEN 114      ELSE 015    END AS is_new_session16  FROM ordered17)18SELECT *,19  SUM(is_new_session) OVER (20    PARTITION BY user_id ORDER BY ts, event_id21    ROWS UNBOUNDED PRECEDING22  ) AS session_n23FROM flagged;

Running totals, moving averages, frames

sql

1SELECT2  day,3  gmv,4  SUM(gmv) OVER (5    ORDER BY day6    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW7  ) AS gmv_ytd,8  AVG(gmv) OVER (9    ORDER BY day10    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW11  ) AS gmv_ma712FROM daily_gmv;1314-- Partitioned running total (per country)15SUM(gmv) OVER (16  PARTITION BY country17  ORDER BY day18  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW19)

ROWS vs RANGE - the interview trap

ROWS: physical offsets. RANGE: logical value range on the ORDER BY expression (peers with equal keys). RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all peers of the current order key. For monetary running totals with possible same-day rows, ROWS is clearer.

sql

1-- Same day two orders: RANGE running SUM can jump including both peers at once2SUM(amount) OVER (PARTITION BY user_id ORDER BY day)  -- default RANGE frame often34-- Explicit safer running sum by row sequence5SUM(amount) OVER (6  PARTITION BY user_id7  ORDER BY day, order_id8  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW9)

QUALIFY and filtering window results

You cannot put window functions in WHERE of the same SELECT level. Use subquery/CTE, or QUALIFY (Snowflake/BigQuery/DuckDB). In PostgreSQL: wrap and filter rn = 1 outside.

sql

1-- Snowflake/BigQuery style2SELECT *3FROM orders4QUALIFY ROW_NUMBER() OVER (5  PARTITION BY user_id ORDER BY created_at DESC6) = 1;78-- Postgres style: CTE + WHERE

Windows vs self-joins vs GROUP BY

01"First event per user?" → ROW_NUMBER partition user order ts, keep rn=1; deterministic tie-break.
02"RANK vs ROW_NUMBER?" → RANK shares ties with gaps; ROW_NUMBER unique; DENSE_RANK no gaps.
03"Running total frame?" → prefer ROWS UNBOUNDED PRECEDING AND CURRENT ROW; watch RANGE peers.
04"LAG null first row?" → expected; COALESCE only if product wants a default.
05"Window in WHERE?" → illegal same level; subquery or QUALIFY.
06"When GROUP BY instead?" → when you want collapsed grain, not decorated row-level detail.
07"Moving average 7d?" → AVG ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ordered by day.
08"Top-N per group?" → rank/dense_rank/row_number then filter rnk <= N.
09"Sessionize?" → LAG gap flags + cumulative sum of new-session flags.
10"Performance note?" → windows sort per partition; reduce columns first; index/sort keys help some engines.

docsPostgreSQL window functionsPostgreSQL docsPostgreSQL window function calls (frames)PostgreSQL articleMode - window functionsMode docsSnowflake QUALIFYSnowflake

Interview habit: before you write a join or window, say the grain of the result set out loud (one row per what?), name the filters that apply before aggregation, and sketch a 4-row toy table. That 30-second ritual catches double-counting and null traps faster than staring at syntax.

When stuck live, reduce to two tables and three rows on the whiteboard. Prove the join, then reintroduce filters and aggregates. Interviewers credit recovery more than a first-draft perfect query that you cannot explain.

State timezone and late-arriving data assumptions once. 'UTC calendar day of paid_at; events may land +2h' prevents half the metric arguments in marketplace and consumer analytics rounds.

Prefer COUNT(DISTINCT key) or EXISTS for existence metrics; prefer SUM of a measure that already lives at the target grain. If you must join a child table, aggregate the child in a CTE first and say why.

Read the English noun in the question: users, orders, sessions, days. That noun is usually the GROUP BY or partition key. If your SELECT invents a different noun, you probably changed the grain by accident.

Null policy belongs in the spoken answer: 'missing country is excluded from geo breakouts but included in totals' is a product decision. Silent COALESCE to 'unknown' without saying so is how dashboards disagree.

For top-N, always name the tie-break (latest id, lowest sku, highest created_at). Nondeterministic winners fail both unit tests and interviewer follow-ups about flaky results.

Checkpoint

Return each user's most recent paid order row. Best core pattern?

AGROUP BY user_id with MAX(created_at) only, selecting * from ordersBROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_id DESC) FILTER paid then keep rn=1CDISTINCT ON (user_id) without ORDER BY in Postgres

Checkpoint

Running SUM(amount) OVER (ORDER BY day) with two orders on the same day shows equal cumulative totals on both rows unexpectedly. Likely cause?

ASUM cannot be used as a window functionBDefault RANGE frame treats same ORDER BY day as peers, so both rows include each other in the frame togetherCPARTITION BY was required and its absence zeros the sum

Checkpoint

RANK() vs ROW_NUMBER() for prize places when two users tie for first?

ABoth always assign 1 and 2 uniquelyBRANK gives both rank 1 then next is 3; ROW_NUMBER still gives distinct 1 and 2 using tie-break orderCDENSE_RANK is identical to ROW_NUMBER on ties

Checkpoint

You write WHERE ROW_NUMBER() OVER (...) = 1 in PostgreSQL. Result?

AWorks - windows are allowed in WHEREBError or invalid: filter windows via subquery/CTE (or QUALIFY in other dialects)CSilently ignored and returns all rows

Checkpoint

7-day moving average of daily GMV including today - frame?

AROWS BETWEEN 7 PRECEDING AND 7 FOLLOWINGBROWS BETWEEN 6 PRECEDING AND CURRENT ROW ordered by dayCRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Can you pick rank vs row_number, write a running total with an explicit ROWS frame, and filter top-N via CTE?

New to itGetting thereConfident

Takeaways

Windows decorate rows; GROUP BY collapses them.
Always PARTITION + ORDER with deterministic tie-breaks.
ROWS vs RANGE changes running metrics on peers.
Filter window results in outer query or QUALIFY.
LAG + cumulative flags sessionize; name edge cases.

Next: CTEs, debugging broken SQL, and reading plans at interview altitude.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free