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.
Understanding SQL Window Functions Reference
SQL evaluates a query in stages. First it chooses tables, joins them, filters records, and forms any groups. Window calculations normally cannot be placed directly in WHERE, because WHERE has already decided which records survive.
If a report needs to filter by a calculated rank, place the window calculation in a subquery or common table expression, then filter in an outer query. This two-step design is common for finding the top three products within every category.
GROUP BY behaves differently. It combines rows into one result per group, so choose it when individual transactions are no longer needed.
The order inside a window is not necessarily the order of the final results. A query can rank students by score, then display the results alphabetically with a final ORDER BY. These are separate jobs.
Ties need careful thought. If two students have the same score, a ranking function follows its tie rule. If ROW_NUMBER is used with only score, the database may choose either tied student first.
Add a stable extra field, such as student ID, when a unique and repeatable order matters. This is important in reports, grading records, and automated selections.
Frames are where many running calculations become confusing. ROWS counts actual row positions. RANGE often treats rows with equal ordering values as one peer group.
Imagine two sales recorded on the same date. With a ROWS frame, the first sale can have a smaller running total than the second sale. With a RANGE frame, both may receive the same total because both share the date.
Database systems can use different default frames when an order is present. Writing the frame explicitly prevents a result from changing because of duplicate dates or scores.
LAG and LEAD are useful for measuring change over time. A utility company can compare each meter reading with the previous recorded reading. A shop can compare this month's sales with the next month's forecast.
The first row has no previous row, so LAG often returns NULL there. The last row has the same issue with LEAD. Treat NULL as missing information rather than zero unless zero is genuinely correct.
Also remember that the previous row means the previous recorded row, not necessarily yesterday or last week. Test a small sample by hand, especially when dates are missing, values are duplicated, or partitions contain only one row.