SQL window functions let you calculate values across related rows without collapsing the result into fewer rows. This cheat sheet helps students compare rows, rank records, compute running totals, and analyze trends inside query results. It is useful for data analysis tasks where each original row must stay visible.
Students need these patterns to write clearer queries for reports, dashboards, and database projects.
The main idea is that a window function uses OVER to define the group and order of rows used in a calculation. PARTITION BY splits the data into separate groups, while ORDER BY controls sequence inside each group. Ranking functions assign positions, aggregate window functions compute totals or averages over a window, and LAG or LEAD compare a row with nearby rows.
Frame clauses such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW define exactly which rows are included.
Key Facts
- A window function uses the pattern function_name() OVER (PARTITION BY column ORDER BY column) to calculate across rows while keeping each row in the output.
- PARTITION BY creates separate windows, so SUM(sales) OVER (PARTITION BY region) calculates a regional total for every row in that region.
- ORDER BY inside OVER sets the row sequence, so ROW_NUMBER() OVER (ORDER BY score DESC) gives 1 to the highest score.
- ROW_NUMBER() assigns unique sequential numbers, while RANK() gives tied rows the same rank and leaves gaps after ties.
- DENSE_RANK() gives tied rows the same rank but does not leave gaps, so ranks can be 1, 2, 2, 3.
- Running totals use a frame such as SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
- LAG(value, 1) OVER (ORDER BY date) returns the previous row value, and LEAD(value, 1) OVER (ORDER BY date) returns the next row value.
- Window functions are evaluated after WHERE filtering, so rows removed by WHERE are not available inside the window.
Vocabulary
- Window Function
- A SQL function that calculates a value across a set of related rows while returning one result row for each original row.
- OVER Clause
- The part of a window function that defines which rows are included and how they are ordered.
- PARTITION BY
- A clause that divides rows into separate groups so the window function runs independently within each group.
- ORDER BY
- A clause that sets the order of rows inside a window, which is required for ranking and previous or next row comparisons.
- Frame
- The subset of rows within an ordered window used by an aggregate calculation, such as all earlier rows through the current row.
- Ranking Function
- A window function such as ROW_NUMBER, RANK, or DENSE_RANK that assigns a position to each row based on an order.
Common Mistakes to Avoid
- Using GROUP BY when each original row must remain visible is wrong because GROUP BY collapses rows, while window functions keep row-level detail.
- Leaving out ORDER BY for ranking functions is wrong because the database has no reliable sequence for assigning ranks or row numbers.
- Confusing RANK with DENSE_RANK is wrong because RANK skips numbers after ties, while DENSE_RANK continues with the next number.
- Putting a window function in the WHERE clause is wrong in many SQL systems because window functions are calculated after WHERE filtering.
- Forgetting the frame in a running total can be wrong because the default frame may not match the intended row-by-row calculation in every database.
Practice Questions
- 1 Write a SQL expression that assigns each student a unique number from highest to lowest score using the column score.
- 2 A table sales has columns region, sale_date, and amount. Write a window expression that calculates a running total of amount by sale_date within each region.
- 3 Scores are 100, 95, 95, and 90 in descending order. What ranks are produced by RANK(), and what ranks are produced by DENSE_RANK()?
- 4 Explain why a window function is better than GROUP BY when you need each employee row plus the department average salary on the same row.