Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD

WHY do window functions exist?

• GROUP BY → N rows in, fewer rows out (collapse).
• Window function → N rows in, N rows out (annotate).

That single difference is 80% of the value. Memorise it.

WHAT is the anatomy of a window?

• PARTITION BY = "restart the calculation for each group" (like a per-group reset).
• ORDER BY = "in what sequence do I walk through the rows" (needed for ranking & lag/lead).
• If you omit PARTITION BY, the whole result set is one partition.

The five core functions

HOW the three rankers differ (derive it!)

Take ordered salaries (DESC): 900, 900, 700, 500.

Derivation logic:

• ROW_NUMBER just increments: position in the line.
• RANK = 1 + (number of rows strictly ahead of you). Two 900s → the 700 has 2 rows ahead → rank 3.
• DENSE_RANK = 1 + (number of distinct values strictly ahead). The 700 has 1 distinct value ahead (900) → rank 2.

That formula is the definition — no memorising the gap rule.

$\text{RANK}(r) = 1 + \big|\{x : x \prec r\}\big|, \qquad \text{DENSE\_RANK}(r) = 1 + \big|\{\text{distinct } v : v \prec r\}\big|$

Worked Example 1 — Top earner per department

SELECT name, dept, salary
FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

• Why PARTITION BY dept? We want the counter to restart for each department, so rn=1 means "top in this dept".
• Why ORDER BY salary DESC? rn=1 must land on the highest salary.
• Why the subquery? You ==cannot use a window function in WHERE== (it's computed after WHERE). So compute it in an inner query, filter outside.

Worked Example 2 — RANK vs DENSE_RANK pitfall

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS r,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

• Why might "top 3 salaries" break with RANK? If two people tie for 2nd, RANK gives 1,2,2,4 — so WHERE r <= 3 returns only 3 rows but skips the value that would've been 3rd-distinct.
• Why this step matters: "Top 3 distinct salary levels" → use DENSE_RANK. "Top 3 people by position" → use ROW_NUMBER. Choosing the wrong ranker is the #1 interview trap.

Worked Example 3 — Day-over-day change with LAG

SELECT day, revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_rev,
       revenue - LAG(revenue, 1, 0) OVER (ORDER BY day) AS delta
FROM sales;

• Why LAG? We need the previous row's value beside the current row to compute a difference — exactly what window functions give without a self-join.
• Why the 0 default? The very first day has no predecessor; without a default it'd be NULL, making delta NULL. 0 is a sane fallback.
• Why ORDER BY day? "Previous" is meaningless without an order — LAG follows the window's ordering.

Common mistakes

Flashcards

Connections

• GROUP BY and Aggregate Functions — the collapsing cousin; windows annotate instead.
• Common Table Expressions (CTE) — clean way to filter on window results.
• SQL Logical Query Processing Order — explains why windows run after WHERE.
• Self Joins — what LAG/LEAD replace for "previous/next row" problems.
• Window Frames ROWS vs RANGE — deeper control over running totals/moving averages.
• Subqueries and Derived Tables — wrapping windows to filter them.

Concept Map

Hinglish (regional understanding)