SQL Window Functions
Ranking, partitioned frames, LAG/LEAD, NTILE, running totals, and moving averages in SQL.
12 questions
JuniorTheoryVery commonHow do PARTITION BY and GROUP BY differ, and why does a window function keep every row?
How do PARTITION BY and GROUP BY differ, and why does a window function keep every row?
GROUP BY collapses each group into one output row, losing row-level detail. PARTITION BY only defines the window a function computes over and returns a value for every input row, so the rows survive next to the group result.
Common mistakes
- ✗Assuming a window aggregate collapses rows like
GROUP BY - ✗Swapping which clause preserves row-level detail
- ✗Thinking
PARTITION BYfilters rows out of the result
Follow-up questions
- →How would you get the group total AND collapse to one row per group?
- →Can a query use both
GROUP BYand a window function together?
JuniorTheoryVery commonROW_NUMBER vs RANK vs DENSE_RANK — how does each treat ties, and which skips numbers?
ROW_NUMBER vs RANK vs DENSE_RANK — how does each treat ties, and which skips numbers?
ROW_NUMBER gives every row a distinct number, ordering ties arbitrarily. RANK gives tied rows the same number but skips the next values, so two firsts are 1,1,3. DENSE_RANK ties them with no gaps: 1,1,2. Only RANK skips.
Common mistakes
- ✗Thinking
ROW_NUMBERgives tied rows the same number - ✗Swapping which of
RANK/DENSE_RANKleaves gaps - ✗Believing
DENSE_RANKandRANKproduce identical output
Follow-up questions
- →Which function would you use to pick exactly one row per group?
- →How do you make
ROW_NUMBERdeterministic when the sort key has ties?
JuniorCodeCommonShow each employee's salary next to their department's average on the same row
Show each employee's salary next to their department's average on the same row
Use a window aggregate, not GROUP BY: AVG(salary) OVER (PARTITION BY department). The partition scopes the average per department, yet returns a value for every row, so each salary sits beside its department average.
Common mistakes
- ✗Reaching for
GROUP BY, which collapses the employee rows - ✗Returning one row per department instead of per employee
- ✗Omitting
PARTITION BY, giving the global average instead of per-department
Follow-up questions
- →How would you also flag employees paid above their department average?
- →What does
OVER ()with no partition compute here?
MiddleCodeCommonMonth-over-month revenue change with LAG, labelled increase / decrease / no change
Month-over-month revenue change with LAG, labelled increase / decrease / no change
LAG(revenue) OVER (ORDER BY month) pulls the prior month onto the current row; subtract for the change. A CASE on its sign labels each month increase, decrease, or no change. The first month's LAG is NULL.
Common mistakes
- ✗Using
LEAD(next row) when you needLAG(previous row) - ✗Labelling the first month instead of leaving its trend null
- ✗Adding
PARTITION BY month, which isolates each month soLAGis always null
Follow-up questions
- →How would you compute the percent change rather than the absolute change?
- →What happens to the first month's row and how should you present it?
MiddleCodeCommonReturn the second-highest salary in each department
Return the second-highest salary in each department
Rank salaries per department, then filter to rank 2 outside — a window result can't go in WHERE. DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) ties equal salaries, so rank 2 is the 2nd distinct value.
Common mistakes
- ✗Using
ROW_NUMBER, which splits tied top salaries and shifts rank 2 - ✗Applying
LIMIT/OFFSETglobally instead of per department - ✗Forgetting the window result must be filtered in an outer query
Follow-up questions
- →How would the answer change if you wanted the second-highest employee, ties included?
- →Why does
DENSE_RANKbeatROW_NUMBERwhen salaries can tie?
MiddleCodeCommonReturn the top 3 products by sales in every category
Return the top 3 products by sales in every category
Aggregate revenue by product, rank per category, keep top ranks outside: ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) with WHERE rn <= 3. GROUP BY ... LIMIT 3 caps globally, not per category.
Common mistakes
- ✗Thinking
LIMITcaps rows per category rather than globally - ✗Using
HAVING COUNT(*)as if it selected the top rows - ✗Assuming
RANKalways yields exactly three rows before a gap
Follow-up questions
- →How would ties for third place change the row count with
RANKvsROW_NUMBER? - →Why can't
GROUP BY ... LIMIT 3solve the per-category cap?
MiddleCodeOccasionalCompute a 7-day trailing moving average of daily revenue
Compute a 7-day trailing moving average of daily revenue
Use an explicit frame: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The current row plus the six before it form a 7-row trailing window. ROWS counts rows, so it needs one row per day.
Common mistakes
- ✗Assuming the default
ORDER BYframe is a 7-day window, not cumulative - ✗Mixing up
PRECEDINGandFOLLOWINGfor a trailing window - ✗Replacing the frame with a fixed weekly
GROUP BYbucket
Follow-up questions
- →How does the answer change if some days are missing from the table?
- →Why is
ROWSpreferred overRANGEfor a fixed-count moving average?
JuniorTheoryRareWhy can't a window function go in WHERE, and how do you filter on its result?
Why can't a window function go in WHERE, and how do you filter on its result?
Window functions run after WHERE, GROUP BY, and HAVING, in the SELECT phase, so their result doesn't exist when WHERE runs. To filter, compute the window in a subquery or CTE, then filter the outer query on its alias.
Common mistakes
- ✗Thinking
HAVINGcan filter a window result without a wrapper - ✗Believing a
SELECTalias is visible toWHERE - ✗Attributing the restriction to performance rather than evaluation order
Follow-up questions
- →Where in the logical query order do window functions run?
- →Why can't
HAVINGfilter a window function either?
MiddleDebuggingRareLAST_VALUE returns each row's own salary instead of the department's top salary
LAST_VALUE returns each row's own salary instead of the department's top salary
With ORDER BY, the default RANGE frame ends at the current row, so LAST_VALUE returns that row's own salary. Widen it to UNBOUNDED FOLLOWING, or just use MAX(salary) OVER (PARTITION BY department).
Common mistakes
- ✗Blaming the sort direction rather than the frame default
- ✗Thinking
PARTITION BYrestarts per row instead of per group - ✗Expecting
LAST_VALUEto scan the whole partition by default
Follow-up questions
- →Why does
FIRST_VALUEwork as expected here butLAST_VALUEdoesn't? - →When would you prefer
MAX() OVER (...)overLAST_VALUE?
MiddleDebuggingRareA running total over a date column jumps on tied dates — ROWS vs RANGE
A running total over a date column jumps on tied dates — ROWS vs RANGE
The default RANGE frame's CURRENT ROW covers every peer sharing the same sale_date, so a tied date shows the total through that whole date, not a per-row step. Fix it with ROWS plus a unique tiebreaker like id.
Common mistakes
- ✗Blaming duplicate rows instead of the
RANGEpeer semantics - ✗Believing
ROWSandRANGEbehave identically - ✗Adding
PARTITION BY sale_date, which resets the total per date
Follow-up questions
- →How does
RANGEdefine a peer, and why does that matter here? - →What does adding a unique
idto theORDER BYguarantee?
