SQL · Quick Reference

Lesson 11 — Window Functions Cheat Sheet

SQL

In one line: GROUP BY collapses rows into one row per group. Window functions add an extra computed column to every row while keeping all rows visible.

Key Ideas

1What Are Window Functions?. GROUP BY collapses rows into one row per group. Window functions add an extra computed column to every row while keeping all rows visible.

2PARTITION BY and ORDER BY in OVER. PARTITION BY splits rows into partitions (like GROUP BY, but without collapsing). ORDER BY inside OVER defines the order for ranking or running calculations.

3Ranking Functions. SQL provides four ranking functions that differ in how they handle ties:

4LAG and LEAD — Row Comparisons. LAG(col, n, default) looks back n rows; LEAD(col, n, default) looks forward n rows. Perfect for period-over-period comparisons.

5Running Totals with SUM OVER. Adding ORDER BY inside SUM() OVER creates a running (cumulative) total. You can also define a row frame for moving averages.

Code Examples

-- Without window function (collapses rows)
SELECT grade, AVG(age) FROM students GROUP BY grade;

-- With window function (keeps all rows + adds aggregate column)
SELECT name, grade, age,
  AVG(age) OVER (PARTITION BY grade) AS avg_age_in_grade
FROM s...

SELECT name, grade, age,
  ROW_NUMBER() OVER (PARTITION BY grade ORDER BY age DESC) AS rank_in_grade
FROM students;

SELECT name, score,
  ROW_NUMBER()   OVER (ORDER BY score DESC) AS row_num,   -- unique rank
  RANK()         OVER (ORDER BY score DESC) AS rank_val,  -- gaps for ties
  DENSE_RANK()   OVER (ORDER BY score DESC) AS dense_rank, -- no gaps
  NTILE(4)     ...