Lesson Description
Look at the row before/after; accumulate values.
Lesson 02
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.
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:
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.
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.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_name | hire_date | salary | prev_salary | next_hire |
|---|---|---|---|---|
| Lex | 2001-01-13 | 17000.00 | NULL | Susan |
| Susan | 2002-06-07 | 6500.00 | 17000.00 | Daniel |
| Daniel | 2002-08-16 | 9000.00 | 6500.00 | Nancy |
| Nancy | 2002-08-17 | 12000.00 | 9000.00 | Den |
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.
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.
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.
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.
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;
| month | revenue | change_vs_prev | running_revenue |
|---|---|---|---|
| 2025-01-01 | 918.72 | NULL | 918.72 |
| 2025-02-01 | 758.39 | -160.33 | 1677.11 |
| 2025-03-01 | 1523.86 | 765.47 | 3200.97 |
| 2025-04-01 | 1616.74 | 92.88 | 4817.71 |
| 2025-05-01 | 1296.51 | -320.23 | 6114.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.
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.
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 ...).
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.
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.
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).
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.
LAG() reads a value from an earlier row and LEAD() from a later one, based on the ORDER BY inside OVER.NULL at the edges.PARTITION BY keeps neighbor lookups and running totals inside each group.OVER (ORDER BY ...) becomes a running total; order it by something unique so tied rows do not jump together.WHERE, so decide whether to filter before or after.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.
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.
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
3 quick questions. One at a time. Instant score at the end.
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here
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.
No results yet
Run a query to see results here