Skip to main content
Window Functions
2 / 4
0%

LAG, LEAD, and running totals

0/3 solved3 to unlock next
PrevLocked

Lesson 02

LAG, LEAD, and running totals

Lesson Description

Look at the row before/after; accumulate values.

Easy Project

Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.

Comparing a row with its neighbors

In the previous lesson you ranked rows. Ranking answers "where does this row stand?" A different family of questions is about sequence: "what happened just before this?", "what happens next?", and "how much has piled up so far?"

Here are a few you could ask of the sandboxes:

  • In the store, how many days pass between one customer's orders?
  • Month by month, did revenue go up or down compared with the month before?
  • In the hospital, how long has it been since each patient's last appointment?

Before window functions, answering these meant joining a table to itself on some "previous row" condition, which is fiddly and slow. LAG() and LEAD() let you reach into the previous or next row directly, and SUM() or COUNT() with OVER (ORDER BY ...) gives you running totals in one line.

The syntax

LAG(expression [, offset [, default]])  OVER (PARTITION BY ... ORDER BY ...)
LEAD(expression [, offset [, default]]) OVER (PARTITION BY ... ORDER BY ...)

SUM(expression)   OVER (PARTITION BY ... ORDER BY ...)   -- running total
COUNT(*)          OVER (PARTITION BY ... ORDER BY ...)   -- running count
  • expression is the value you want to fetch from the other row. It can be any column or calculation.
  • offset is how many rows to move. It defaults to 1.
  • default is what to return when there is no row that far away. It defaults to NULL.
  • ORDER BY inside OVER defines what "previous" and "next" mean. Without it, the neighbors are meaningless.
  • PARTITION BY keeps the lookups inside a group, so the first order of one customer never peeks at another customer's last order.

Example 1: the previous and next hire

Start in the HR sandbox. For each employee, in order of hire date, show the salary of the person hired just before them and the name of the person hired just after:

SELECT first_name, last_name, hire_date, salary,
       LAG(salary)      OVER (ORDER BY hire_date, id) AS prev_salary,
       LEAD(first_name) OVER (ORDER BY hire_date, id) AS next_hire
FROM employees
ORDER BY hire_date, id
LIMIT 4;
first_namehire_datesalaryprev_salarynext_hire
Lex2001-01-1317000.00NULLSusan
Susan2002-06-076500.0017000.00Daniel
Daniel2002-08-169000.006500.00Nancy
Nancy2002-08-1712000.009000.00Den

Lex De Haan was hired first, so there is nobody before him and prev_salary is NULL. At the other end of the table, the most recent hire would have a NULL for next_hire. Notice the id after hire_date: adding a unique column guarantees that "previous" is always well defined, even if two people were ever hired on the same day.

Example 2: offsets and defaults

The optional arguments change how far you look and what you get at the edge:

SELECT first_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY hire_date, id) AS prev_or_zero,
       LAG(salary, 2)    OVER (ORDER BY hire_date, id) AS two_back
FROM employees
ORDER BY hire_date, id
LIMIT 3;

For Lex, prev_or_zero is 0 instead of NULL, and two_back is NULL because there are no two earlier rows. Daniel, the third hire, is the first row where two_back has a value: 17000.00, Lex's salary.

Use the default argument with care. A 0 is convenient for arithmetic, but it can also lie. "The previous salary was 0" is not the same as "there was no previous employee". Often NULL is the more honest answer.

Example 3: gaps between a customer's orders

Now add PARTITION BY so each customer is treated as a separate sequence. Subtracting one date from another in PostgreSQL gives a whole number of days:

SELECT customer_id, id AS order_id, order_date,
       LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, id) AS prev_order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, id) AS days_since_prev
FROM orders
WHERE customer_id IN (1, 5, 13)
ORDER BY customer_id, order_date;

This returns 8 rows. Customer 1 ordered twice, 44 days apart. Customer 5 ordered three times with gaps of 44 and 45 days. Customer 13 came back faster, after 16 days and then 21 days. Each customer's first order shows NULL because the partition restarts.

Remove the WHERE and run it across all 50 orders: 20 rows have no previous order (one per customer who has ordered), and the other 30 are repeat purchases. The gaps range from 9 to 93 days, averaging about 41.9 days. That is a real retention metric, computed in one query.

The same idea works in the hospital sandbox: LAG(appointment_date) OVER (PARTITION BY patient_id ORDER BY appointment_date, id) across all 66 appointments shows 36 follow-up visits, with gaps from 4 to 90 days.

Example 4: running totals

When you give an aggregate like SUM() or COUNT() an OVER (ORDER BY ...), it stops meaning "the total for the group" and starts meaning "the total so far". Here is a running count of store orders:

SELECT order_date, id,
       COUNT(*) OVER (ORDER BY order_date, id) AS orders_so_far
FROM orders
ORDER BY order_date, id;

The first row (order 1001 on 2025-01-05) shows 1, the second shows 2, and the last row, order 1021 on 2025-05-12, shows 50. Swap COUNT(*) for SUM(salary) in the HR table, ordered by hire date, and you get the cumulative payroll as the company grew.

Example 5: month over month, with a running total

This example combines everything: aggregate first, then look backwards and accumulate. It reports store revenue per month, excluding cancelled orders:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY 1
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change_vs_prev,
       SUM(revenue) OVER (ORDER BY month)           AS running_revenue
FROM monthly
ORDER BY month;
monthrevenuechange_vs_prevrunning_revenue
2025-01-01918.72NULL918.72
2025-02-01758.39-160.331677.11
2025-03-011523.86765.473200.97
2025-04-011616.7492.884817.71
2025-05-011296.51-320.236114.22

Five rows, one per month. February dipped, March nearly doubled, and the year to date reached 6,114.22 by May. Because each month appears only once in the CTE, ORDER BY month is already unique and needs no tiebreaker.

Under the hood

Two details explain most surprises with these functions.

Window functions see the rows left after WHERE. In Example 3, the WHERE customer_id IN (1, 5, 13) runs first, and only then are neighbors calculated. That was harmless because each partition is one customer. But if you filtered WHERE status = 'delivered', LAG would skip right over cancelled orders and report the gap to the previous delivered order. Sometimes that is what you want, sometimes not. If you need the full sequence but only want to display some rows, calculate in a CTE and filter in the outer query.

Running totals include peers. When an aggregate has ORDER BY in its window, PostgreSQL's default frame is "from the start of the partition up to the current row and every row that ties with it". You will meet frames properly in the last lesson of this course. For now, the practical rule is: order a running total by something unique.

Common mistakes

Forgetting the OVER clause

SELECT first_name, LAG(salary) FROM employees;
ERROR:  window function lag requires an OVER clause

LAG and LEAD only exist as window functions. Add OVER (ORDER BY ...).

Ordering a running total by a column with duplicates

SELECT department_id, salary,
       SUM(salary) OVER (ORDER BY department_id) AS running
FROM employees
WHERE department_id IN (10, 20, 30);

You expect the total to climb row by row. Instead, both Marketing rows (department 20) show 23,400.00 and all six Purchasing rows (department 30) show 48,300.00. Rows with the same department_id are peers, so they are all added at once. The fix is a unique tiebreaker: ORDER BY department_id, id.

Filtering on LAG in the same query

Just like rankings, WHERE days_since_prev > 60 fails because the window value does not exist yet when WHERE runs. Put the calculation in a CTE and filter outside it.

Other databases

LAG and LEAD with the offset and default arguments work the same way in SQL Server 2012+, Oracle, MySQL 8.0+ and SQLite 3.25+. What differs is date arithmetic: order_date - other_date returns days in PostgreSQL, but in MySQL you would use DATEDIFF(order_date, other_date), and in SQL Server DATEDIFF(day, other_date, order_date).

Practice

Three exercises follow. The first, in the store sandbox, asks for a running count of orders. The second, in HR, asks how each new hire's salary compares with the person hired just before. The third, back in the store, asks for the date of the next order. In each one, pay attention to the order the prompt specifies, including the tiebreaker, because the grader compares rows in order.

Key takeaways

  • LAG() reads a value from an earlier row and LEAD() from a later one, based on the ORDER BY inside OVER.
  • The optional offset moves further than one row; the optional default replaces the NULL at the edges.
  • PARTITION BY keeps neighbor lookups and running totals inside each group.
  • An aggregate with OVER (ORDER BY ...) becomes a running total; order it by something unique so tied rows do not jump together.
  • Window functions only see rows that survive WHERE, so decide whether to filter before or after.

LAG, LEAD, and running totals: in depth

LAG and LEAD read values from the previous or next row, which is how you compute change over time: growth month over month, the gap between appointments, or days since the last order. Running totals use SUM as a window to show cumulative progress.

Worked examples

Monthly orders and change from the previous month

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY 1
)
SELECT month, orders,
       orders - LAG(orders) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

LAG(orders) looks one row back in month order. The first month has no previous row, so change is NULL.

Running payroll by hire date

SELECT first_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date, id) AS running_payroll
FROM employees
ORDER BY hire_date, id;

Each row shows the total salary of everyone hired up to and including that person.

Common mistakes

  • Leaving ORDER BY out of OVER for LAG, LEAD or a running total. Without it the "previous row" is undefined.
  • Ties in the ORDER BY of a running total: with the default frame, rows sharing a hire_date are all added at once. Add a unique tie-breaker.
  • Ignoring the NULL on the first row. LAG(orders, 1, 0) supplies a default when there is no previous row.

Other databases

  • LAG and LEAD are standard and available in PostgreSQL, SQL Server 2012+, Oracle, SQLite 3.25+ and MySQL 8.0+.
  • GROUP BY 1 (by position) works in PostgreSQL and MySQL but not in SQL Server.
Check yourself: Two employees share the same hire_date and the window is ORDER BY hire_date only. Do their running totals differ?

No. The default RANGE frame treats tied rows as peers, so both show the total including both salaries. Add id to the ORDER BY to separate them.

Concept check

Quick check: LAG, LEAD, and running totals

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Running total of orders by date

Commerce Operations IntelligencehardPostgreSQL

From the store sandbox, return one row per order with order_date, id (order_id), and a running_orders column showing the cumulative count of orders up to and including that date, ordered by order_date then id.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Salary gap between consecutive hires

Enterprise Workforce AnalyticseasyPostgreSQL

Return first_name, last_name, hire_date, salary, and salary_diff โ€” the difference between this employee's salary and the previous hire's salary (ordered by hire_date, then id). Use LAG.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Next order date per order

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return id (alias order_id), order_date, and next_order_date (the date of the following order when sorted by order_date, id). Use LEAD.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Window Functions
2 / 4
0%

LAG, LEAD, and running totals

0/3 solved3 to unlock next
PrevLocked