Priya needs to rank salespersons by revenue within each region — and show each person's revenue compared to the previous month. GROUP BY collapses the data into one row per group. She needs all rows intact, with the calculation alongside. That is exactly what window functions do.

What makes window functions different

Aggregate functions (SUM, COUNT, AVG) collapse multiple rows into one. Window functions perform calculations across a set of rows but return one result per row — the data is never collapsed. You keep all your detail and get the summary alongside it.

GROUP BYCollapses rows
OVER()Keeps all rows
+Both in one query

The OVER clause

Every window function uses the OVER clause to define its "window" — the set of rows it looks at for each calculation. An empty OVER() means the entire result set. PARTITION BY divides rows into groups. ORDER BY within OVER defines the order for ranking and cumulative calculations.

SUM(Revenue) OVER() -- total of entire table
SUM(Revenue) OVER(PARTITION BY Region) -- total per region
SUM(Revenue) OVER(ORDER BY OrderDate) -- running total

ROW_NUMBER and RANK

SELECT
  Salesperson,
  Revenue,
  ROW_NUMBER() OVER(ORDER BY Revenue DESC) AS RowNum,
  RANK() OVER(ORDER BY Revenue DESC) AS Rank_
FROM SalesFact;
💡
ROW_NUMBER vs RANK: ROW_NUMBER always gives unique sequential numbers (1,2,3,4). RANK gives tied rows the same number and then skips (1,2,2,4). Use ROW_NUMBER when you need exactly N rows. Use RANK when ties should be acknowledged.

Running totals with SUM OVER

SELECT
  OrderDate,
  Revenue,
  SUM(Revenue) OVER(ORDER BY OrderDate) AS RunningTotal
FROM SalesFact
ORDER BY OrderDate;

LAG and LEAD for period comparison

LAG looks at the previous row's value. LEAD looks at the next row's value. This is how you build month-over-month or year-over-year comparisons without self-joins.

SELECT
  OrderDate,
  Revenue,
  LAG(Revenue) OVER(ORDER BY OrderDate) AS PrevRevenue,
  Revenue - LAG(Revenue) OVER(ORDER BY OrderDate) AS Change_
FROM SalesFact;
⚠️
Common mistake: Forgetting that LAG on the first row returns NULL (there is no previous row). Always handle this with COALESCE: COALESCE(LAG(Revenue) OVER(...), 0) to treat the first row's "previous" as zero.
🎯 Your Challenge

Write a query that ranks salespersons by total revenue within each region (use PARTITION BY Region), showing their rank, name, and total revenue. Then find who is ranked #1 in each region.

FAQ

No. Window functions are evaluated after WHERE, so you cannot filter on them directly. Wrap the query in a subquery or CTE and filter on the window function result in the outer query.
All major modern databases do (PostgreSQL, SQL Server, MySQL 8+, SQLite 3.25+, BigQuery, Snowflake). MySQL 5.x does not. If you are on an older MySQL version, you will need to use self-joins or variables to simulate window functions.
`