SQL Window Functions — ROW_NUMBER, RANK, LAG, and More
The Interview Question
What are window functions? How are they different from GROUP BY? Give examples of common window functions.
Expert Answer
Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does. With GROUP BY, if you group sales by month, you get one row per month. With a window function, every individual sale row is preserved, but each row can see aggregated data about its month. The syntax is function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY defines the groups (like GROUP BY but without collapsing), and ORDER BY defines the order within each group. Common window functions: ROW_NUMBER assigns a unique sequential number per partition. RANK and DENSE_RANK handle ties — RANK skips numbers after ties (1,1,3), DENSE_RANK doesn't (1,1,2). LAG and LEAD access previous and next row values — essential for period-over-period comparisons. Running totals use SUM() OVER with an ORDER BY and no frame specification (or ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
Key Points to Hit in Your Answer
- Window functions preserve individual rows — GROUP BY collapses them
- PARTITION BY = grouping without collapsing (like GROUP BY for windows)
- ROW_NUMBER: always unique (1,2,3); RANK: gaps after ties (1,1,3); DENSE_RANK: no gaps (1,1,2)
- LAG(col, n): value from n rows before; LEAD(col, n): value from n rows after
- Running total: SUM(amount) OVER (ORDER BY date)
- Frame clause (ROWS BETWEEN) controls which rows the function sees
- Window functions execute after WHERE, GROUP BY, HAVING — before ORDER BY
Code Example
-- Top 3 salespeople per department
SELECT name, department, total_sales,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY total_sales DESC
) AS rank
FROM salespeople
QUALIFY rank <= 3; -- or use subquery with WHERE
-- Month-over-month revenue change
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;
-- Running total
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;
-- Percentile / distribution
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary) AS quartile,
PERCENT_RANK() OVER (ORDER BY salary) AS percentile
FROM employees;What Interviewers Are Really Looking For
Window functions are the #1 'advanced SQL' interview topic. The ROW_NUMBER vs RANK vs DENSE_RANK distinction is asked frequently. Showing LAG for period-over-period comparison and running totals covers the most practical use cases. If asked 'get the top N per group', the ROW_NUMBER + PARTITION BY pattern is the standard answer. Know that window functions run late in the query execution order — you can't use them in WHERE.
Practice This Question with AI Grading
Reading about interview questions is a start — but practicing with real-time AI feedback is how you actually get better. Goliath Prep grades your answers instantly and tells you exactly what you're missing.
Start Practicing Free →