Lesson 7 of 14 · 7 min · Window functions and CTEs
LAG, LEAD and running totals
What you will learn
LAGandLEADto look at the previous and next row- Month-over-month change and percentage growth
- Running totals with
SUM() OVER (ORDER BY ...) - Moving averages with a window frame
- The default frame and why ties matter
In simple words
Business questions are often about change: "How did this month compare with last month?" To answer, each row needs to peek at the row before it. LAG looks one row back and LEAD looks one row forward. A running total is a score that grows as you move down the rows, like a cricket scoreboard adding each over to the total so far.
Words to know
- LAG: gives the value from an earlier row.
- LEAD: gives the value from a later row.
- Running total: a sum that includes all rows up to the current one.
- Frame: the exact range of rows around the current row that the function uses.
Sample data
A small monthly sales table:
CREATE TABLE monthly_sales (month DATE PRIMARY KEY, revenue INT);
INSERT INTO monthly_sales VALUES
('2025-07-01', 40000), ('2025-08-01', 52000),
('2025-09-01', 47000), ('2025-10-01', 61000);
LAG and LEAD
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_sales;
month | revenue | prev_month | next_month
------------+---------+------------+------------
2025-07-01 | 40000 | | 52000
2025-08-01 | 52000 | 40000 | 47000
2025-09-01 | 47000 | 52000 | 61000
2025-10-01 | 61000 | 47000 |
The first row has no previous month, so LAG gives NULL. Give it a default with a third argument: LAG(revenue, 1, 0), or look further back with LAG(revenue, 12) for the same month last year. Without ORDER BY inside OVER, "previous" has no meaning, so always include it. Add PARTITION BY to restart per group, for example per product or per customer.
Month-over-month change
SELECT month, revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_sales;
month | revenue | change | pct_change
------------+---------+--------+------------
2025-07-01 | 40000 | |
2025-08-01 | 52000 | 12000 | 30.0
2025-09-01 | 47000 | -5000 | -9.6
2025-10-01 | 61000 | 14000 | 29.8
August grew 12,000 on 40,000, which is 30.0 percent. September fell 5,000 on 52,000 (minus 9.6 percent). To avoid repeating the LAG, wrap it in a CTE (next lesson). Guard against a zero previous value with NULLIF(LAG(...), 0).
Running totals
Add an aggregate with ORDER BY inside OVER:
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_sales;
The running totals are 40000, 92000, 139000 and 200000. Restart per year or per customer with PARTITION BY:
SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total
Window frames and moving averages
A frame says exactly which rows around the current one to include. A 3-month moving average (current row and the two before it):
SELECT month, revenue,
ROUND(AVG(revenue) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3m
FROM monthly_sales;
The first row averages only itself (40000), the second averages two rows (46000), September averages July to September (46333.33), and October averages August to October (53333.33). The early rows use fewer than three months, which is worth mentioning in a report; some analysts hide those rows.
Frame pieces: ROWS BETWEEN <start> AND <end> where each bound is UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.
The default frame and ties
When you write SUM(x) OVER (ORDER BY d) with no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the ORDER BY value are treated as peers and added together. If two orders share the same date, both appear with the same running total, which can look wrong. For a strictly row-by-row running total, either write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW or add a unique tie-breaker such as the order id to the ORDER BY.
FIRST_VALUE and LAST_VALUE
FIRST_VALUE(revenue) OVER (ORDER BY month) returns the first month's revenue on every row, handy for "index against the first month". Careful with LAST_VALUE: with the default frame it returns the current row's value, not the last row's; add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the real last value.
Common mistakes
- Missing
ORDER BYinOVER, makingLAG,LEADand running totals meaningless. - Not partitioning when each customer or product should have its own sequence.
- Percentage change with a zero previous value causing a divide-by-zero error.
- Tied dates with the default
RANGEframe giving repeated running totals. - Using
LAST_VALUEwith the default frame.
Try it yourself
- Create
monthly_salesand add the previous month's revenue and the change. - Add a running total and the share of the year's total (
revenue / SUM(revenue) OVER ()). - Compute a 2-month moving average.
- For
orders, show each customer's days since their previous order (order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)).
Check yourself
What does LAG(revenue, 1, 0) OVER (ORDER BY month) return for the first month?
0, the default given as the third argument, because there is no earlier row.
How do you make a running total restart for each customer?
Add PARTITION BY customer_id to the OVER clause.
What is the default frame when ORDER BY is used in a window with an aggregate?
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats tied rows as peers.
Which rows does ROWS BETWEEN 2 PRECEDING AND CURRENT ROW include?
The current row and the two rows before it.
Interview questions
How would you calculate month-over-month growth?
Use LAG(revenue) OVER (ORDER BY month) for the previous month, then (revenue - prev) / NULLIF(prev, 0) * 100.
How do you calculate a running total, and how can ties distort it?
SUM(x) OVER (ORDER BY date). With the default RANGE frame, rows with the same date are added together, so use a ROWS frame or a unique tie-breaker.
Find customers who ordered on consecutive days.
Compare each order date with LAG(order_date) per customer and keep rows where the difference is one day.
Summary
LAGandLEADread the previous and next row; use a default for the first or last row.- Running totals are an aggregate with
ORDER BYinOVER;PARTITION BYrestarts them. - Frames such as
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWmake moving averages. - Beware the default
RANGEframe with tied values.
Going deeper
- Window function calls: frames and frame types.
- Built-in window functions:
lag,lead,first_value,ntile. - MySQL window function concepts: frame specification in MySQL.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.