Window Functions and CTEs
What you'll build
A set of analytical SQL queries using window functions for ranking, running totals, and lag/lead comparisons, plus CTEs for readable multi-step queries.
Goals
- Use ROW_NUMBER, RANK, and DENSE_RANK
- Compute running totals with window frames
- Compare rows with LAG and LEAD
- Build readable queries with CTEs
Prerequisites
- GROUP BY and aggregates
- JOIN basics
- ORDER BY
Steps
- 1
Set up sales data
Create a sales table with monthly data for ranking and trend analysis.
CodeCREATE TABLE sales ( id SERIAL PRIMARY KEY, region TEXT NOT NULL, month TEXT NOT NULL, amount INTEGER NOT NULL ); INSERT INTO sales (region, month, amount) VALUES ('North', '2024-01', 10000), ('North', '2024-02', 12000), ('North', '2024-03', 9000), ('South', '2024-01', 8000), ('South', '2024-02', 15000), ('South', '2024-03', 11000), ('East', '2024-01', 7000), ('East', '2024-02', 9000), ('East', '2024-03', 13000);Expected result
Nine rows of sales data across three regions and three months.
- 2
Rank with ROW_NUMBER and RANK
Use ROW_NUMBER() and RANK() with PARTITION BY to rank months within each region by sales amount.
CodeSELECT region, month, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk FROM sales ORDER BY region, rn;Expected result
Each region's months are numbered 1-3 by descending amount; ties share rank.
- 3
Running total with window frame
Compute a cumulative running total of sales per region over time using a window frame clause.
CodeSELECT region, month, amount, SUM(amount) OVER ( PARTITION BY region ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM sales ORDER BY region, month;Expected result
running_total accumulates month by month within each region.
- 4
Month-over-month with LAG
Use LAG() to compare each month's sales to the previous month, computing the percentage change.
CodeSELECT region, month, amount, LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month, ROUND( (amount - LAG(amount) OVER (PARTITION BY region ORDER BY month)) * 100.0 / LAG(amount) OVER (PARTITION BY region ORDER BY month), 1 ) AS pct_change FROM sales ORDER BY region, month;Expected result
Each row shows the previous month's amount and the percent change.
- 5
CTE for top performer per region
Use a CTE (WITH clause) to first rank rows, then filter to the top performer per region. CTEs make complex queries readable.
CodeWITH ranked AS ( SELECT region, month, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales ) SELECT region, month AS best_month, amount AS best_amount FROM ranked WHERE rn = 1 ORDER BY region;Expected result
Returns the single best month for each region.
Try it yourself
-- Rank months within each region
SELECT region, month, amount,
??? OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales;WITH ranked AS (
SELECT region, month, amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
)
SELECT * FROM ranked WHERE rn = 1;What you learned
You mastered window functions and CTEs — the tools for analytical SQL. Window functions compute across rows without collapsing them, and CTEs make complex queries readable and maintainable.
Related Resources
Related Lessons
- GROUP BY
Aggregating data.
Related Glossary
- Window Function
Compute across row sets.
- CTE
Common Table Expression.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- Window Function
Compute across row sets.
- CTE
Common Table Expression.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.