Window Functions
Per-row calculations across a related set of rows — running totals, rankings, and row-over-row comparisons — without collapsing rows like GROUP BY does.
Try answering in your head first, then click a question to check the model answer.
Q1.Why would you use a window function instead of `GROUP BY` to compute "each employee's salary compared to their department's average"?(show answer)
GROUP BY would collapse the result to one row per department, losing individual employee rows. A window function like AVG(salary) OVER (PARTITION BY department) computes the department average while still returning every employee row individually, letting you compare each row to its group's aggregate in the same result set.
Q2.Explain what `ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC)` computes.(show answer)
For each row, it assigns a unique, sequential rank (1, 2, 3, ...) within its category partition, ordered by price descending — so the most expensive item in each category gets row number 1, restarting the numbering for each new category.
Q3.How would you find the top 3 highest-paid employees per department using a window function?(show answer)
Compute RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk in a subquery or CTE, then filter the outer query with WHERE rnk <= 3. You can't filter directly on a window function in the same SELECT's WHERE clause, since window functions are evaluated after WHERE.
Q4.What's a practical use case for `LAG()`/`LEAD()`?(show answer)
Computing period-over-period comparisons without a self-join — e.g. amount - LAG(amount) OVER (ORDER BY month) gives each month's change from the previous month directly, which would otherwise require a self-join on month - 1 and is far more verbose and error-prone.