Window Functions: Analytics Without Collapsing Rows
Window functions are the difference between a junior and senior SQL engineer. RANK, LEAD, LAG, running totals, percentiles over partitions — every analytics query at Stripe, Airbnb, and Netflix uses them. This is the lesson that unlocks the entire interview circuit.
Learning Objectives
After this lesson, you will be able to:
Explain why window functions exist and what problem they solve that GROUP BY cannot
Use ROW_NUMBER, RANK, and DENSE_RANK to rank rows within groups
Use LAG and LEAD to access previous and next rows without self-joins
Calculate running totals and moving averages with SUM() OVER and AVG() OVER
Use PARTITION BY to apply window functions within groups independently
Combine ROWS BETWEEN and RANGE BETWEEN to define custom window frames
Use PERCENTILE_CONT and NTILE for percentile analysis on revenue, latency, and ML scores
Your Reflection
Saves automatically
What’s one thing you learned? What’s still confusing?
Window functions can seem abstract until the first time you need one. Then they feel like a superpower. Take your time with this lesson -- once the OVER() clause clicks, you will use window functions constantly.
-- GROUP BY: collapses rows (you lose individual employees)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Result: one row per department
-- Window function: keeps ALL rows, adds the average as a new column
SELECT
name,
department_id,
salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;
-- Result: one row per employee, with department average alongside
The magic is OVER(). Every window function has this clause. It defines the "window" of rows the function looks at.
function_name() OVER (
PARTITION BY column -- divide rows into groups (like GROUP BY, but keeps rows)
ORDER BY column -- sort within each partition
ROWS BETWEEN ... -- define the window frame (which rows to include)
)
PARTITION BY: Resets the function for each group. Like GROUP BY, but rows are not collapsed.
ORDER BY: Determines the order within each partition. Required for ranking and cumulative functions.
ROWS BETWEEN: Defines which rows relative to the current row are included in the calculation.
See how PARTITION BY and the window frame reshape the calculation interactively:
Loading visualization...
sql
-- No PARTITION BY: the window is ALL rows
SELECT name, salary, AVG(salary) OVER () AS company_avg
FROM employees;
-- PARTITION BY department: window is rows in the same department
SELECT name, department_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees;
-- PARTITION BY + ORDER BY: running total within each department
SELECT name, department_id, salary,
SUM(salary) OVER (
PARTITION BY department_id
ORDER BY hire_date
) AS running_dept_salary
FROM employees;
These three functions assign a rank to each row. The difference is how they handle ties:
sql
SELECT
name,
department_id,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, -- 1, 2, 3, 4, 5
RANK() OVER (ORDER BY salary DESC) AS rank_num, -- 1, 2, 2, 4, 5 (skips after tie)
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_num -- 1, 2, 2, 3, 4 (no skip)
FROM employees;
salary
ROW_NUMBER
RANK
DENSE_RANK
120000
1
1
1
95000
2
2
2
95000
3
2
2
80000
4
4
3
75000
5
5
4
ROW_NUMBER: Always unique. Ties get arbitrary order.
RANK: Same rank for ties, then skips. (1, 2, 2, 4)
DENSE_RANK: Same rank for ties, no skip. (1, 2, 2, 3)
-- Find the top 3 highest-paid employees per department
WITH ranked AS (
SELECT
name,
department_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rn
FROM employees
)
SELECT name, department_id, salary
FROM ranked
WHERE rn <= 3;
This is one of the most common SQL interview questions. The pattern is always the same: use ROW_NUMBER() with PARTITION BY, wrap in a CTE, filter by rank.
What Do You Think?
Two employees in the same department have the exact same salary. You use ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC). What happens?
Try it! Rank employees within each department by salary. Try all three ranking functions and compare the output when there are ties.
LAG looks at previous rows. LEAD looks at future rows. Both avoid the need for self-joins.
sql
-- Compare each employee's salary to the previous employee (ordered by hire date)
SELECT
name,
hire_date,
salary,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
salary - LAG(salary, 1) OVER (ORDER BY hire_date) AS salary_change
FROM employees
ORDER BY hire_date;
-- Classic business query: compare each month's revenue to last month
WITH monthly_revenue AS (
SELECT
strftime('%Y-%m', order_date) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
)
SELECT
month,
revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
ROUND(
(revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
/ LAG(revenue, 1) OVER (ORDER BY month),
1
) AS pct_change
FROM monthly_revenue
ORDER BY month;
What Do You Think?
What value does LAG(salary, 1) return for the FIRST row in the result set (there is no previous row)?
-- Running total of sales, ordered by date
SELECT
order_date,
total,
SUM(total) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY order_date;
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means: "from the first row up to this row." This is actually the default when you specify ORDER BY, so you can shorten it:
sql
-- Equivalent (default frame with ORDER BY)
SELECT order_date, total,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;
-- 7-day moving average of daily revenue
SELECT
order_date,
daily_revenue,
AVG(daily_revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7day
FROM daily_sales;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW defines a window of 7 rows: the current row plus the 6 rows before it.
-- ROWS: count physical rows
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW -- current + 3 before
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- current + 1 before + 1 after
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- all rows up to current
-- RANGE: based on value (useful for dates)
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW -- (some DBs)
The real power comes from combining window functions with CTEs for multi-step analytics:
sql
WITH
-- Step 1: Rank employees within each department
ranked_employees AS (
SELECT
name,
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank,
salary - AVG(salary) OVER (PARTITION BY department_id) AS above_dept_avg
FROM employees
),
-- Step 2: Get department-level stats
dept_summary AS (
SELECT
department_id,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
)
-- Step 3: Combine for a rich analysis
SELECT
re.name,
re.department_id,
re.salary,
re.salary_rank,
ROUND(re.above_dept_avg, 2) AS above_avg_by,
ds.headcount,
ds.avg_salary AS dept_avg
FROM ranked_employees re
JOIN dept_summary ds ON re.department_id = ds.department_id
WHERE re.salary_rank <= 3
ORDER BY re.department_id, re.salary_rank;
Window functions feel like a lot of syntax at first, but every query follows the same skeleton: FUNCTION() OVER (PARTITION BY ... ORDER BY ...). Once you internalize that pattern, you just swap in the function name and the partition/order columns. Practice five queries, and it becomes second nature.
Quick Check1 / 5
What is the key difference between GROUP BY and a window function?
What's next: You've now seen the read side of SQL (SELECT, WHERE, joins, aggregates, CTEs, windows). The next lesson covers the write side: INSERT, UPDATE, DELETE, and transactions. Less glamorous but equally critical — and the lesson where you learn why one missing WHERE has wiped production databases multiple times.