Lesson 7 of 14 · 7 min · Window functions and CTEs

LAG, LEAD and running totals

Covers: PostgreSQL 18 syntax; also valid in MySQL 8.0 and later. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • LAG and LEAD to 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 BY in OVER, making LAG, LEAD and 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 RANGE frame giving repeated running totals.
  • Using LAST_VALUE with the default frame.

Try it yourself

  1. Create monthly_sales and add the previous month's revenue and the change.
  2. Add a running total and the share of the year's total (revenue / SUM(revenue) OVER ()).
  3. Compute a 2-month moving average.
  4. 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

  • LAG and LEAD read the previous and next row; use a default for the first or last row.
  • Running totals are an aggregate with ORDER BY in OVER; PARTITION BY restarts them.
  • Frames such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW make moving averages.
  • Beware the default RANGE frame with tied values.

Going deeper

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.