Chapter 1 · Advanced Queries
Window Functions II: LAG, LEAD, Running Totals and Frames
- Page 3 of 22
- 17 min read
On the previous page, window functions numbered and ranked rows without collapsing them. This page uses the same OVER (…) machinery to compute across rows: the value in the previous row, a running total, a three-month moving average, each row's share of the whole. These are the queries behind every revenue dashboard, and they are also how you build time-based features for a model — "spend so far", "days since the last order", "average of the last three months" — without leaving the database.
The new idea that makes all of this precise is the frame: which rows of the partition a function is allowed to see from the current row. Most surprises with window functions are frame surprises, so this page looks at frames carefully.
What you will learn
- Look backwards and forwards with
LAGandLEAD(with offsets and defaults) - Running totals, moving averages and percentage of total with aggregate window functions
- Frames:
ROWSvsRANGEvsGROUPS, the default frame, and theLAST_VALUEtrap - Name a window once with the
WINDOWclause - Turn an order history into per-customer features for analytics and ML
A monthly revenue table to work on
Most examples use monthly revenue: quantity × unit price over every order that was not cancelled. A CTE (see CTEs and Recursive Queries) keeps it readable:
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month, revenue FROM monthly ORDER BY month;+---------+---------+
| month | revenue |
+---------+---------+
| 2026-01 | 6600 |
| 2026-02 | 7000 |
| 2026-03 | 21400 |
| 2026-04 | 23450 |
| 2026-05 | 8500 |
| 2026-06 | 18100 |
+---------+---------+Six rows, one per month. "Not cancelled" also counts the pending order 13, so June is 18,100 here rather than the 3,100 of the delivered-or-shipped report in CTEs and Recursive Queries. The GROUP BY already happened inside the CTE; every window function below works on these six rows without collapsing them.
LAG and LEAD: the row before and the row after
LAG(expr) returns expr from the previous row of the window; LEAD(expr) from the next row. The order comes from ORDER BY inside OVER. Always give it: the databases accept LAG without one, but then "previous" is whatever row the engine happens to read before, which means nothing.
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS change,
LEAD(revenue) OVER (ORDER BY month) AS next_month,
LAG(revenue, 2, 0) OVER (ORDER BY month) AS two_back
FROM monthly
ORDER BY month;+---------+---------+------------+--------+------------+----------+
| month | revenue | prev_month | change | next_month | two_back |
+---------+---------+------------+--------+------------+----------+
| 2026-01 | 6600 | NULL | NULL | 7000 | 0 |
| 2026-02 | 7000 | 6600 | 400 | 21400 | 0 |
| 2026-03 | 21400 | 7000 | 14400 | 23450 | 6600 |
| 2026-04 | 23450 | 21400 | 2050 | 8500 | 7000 |
| 2026-05 | 8500 | 23450 | -14950 | 18100 | 21400 |
| 2026-06 | 18100 | 8500 | 9600 | NULL | 23450 |
+---------+---------+------------+--------+------------+----------+- The first row has no previous row, so
LAGgivesNULL— and so does the arithmetic on it. The last row has no next row. LAG(revenue, 2, 0)looks two rows back and uses0instead ofNULLwhen there is no such row. Use a default only when 0 is truly correct; for "no previous month",NULLis usually the honest answer.LAGcounts rows, not months. If a month had no orders it would simply be missing, andLAGwould compare with two months ago. Build a calendar with a recursive CTE (see CTEs and Recursive Queries) andLEFT JOINto it when gaps are possible.
With PARTITION BY, the previous row is looked up inside each customer's own history. Here is the time between a customer's orders, a classic churn feature:
SELECT customer_id,
order_id,
order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
CAST(julianday(order_date)
- julianday(LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date))
AS INTEGER) AS days_since_prev
FROM orders
WHERE status <> 'cancelled' AND customer_id <= 3
ORDER BY customer_id, order_date;+-------------+----------+------------+------------+-----------------+
| customer_id | order_id | order_date | prev_order | days_since_prev |
+-------------+----------+------------+------------+-----------------+
| 1 | 1 | 2026-01-12 | NULL | NULL |
| 1 | 3 | 2026-02-03 | 2026-01-12 | 22 |
| 1 | 10 | 2026-04-19 | 2026-02-03 | 75 |
| 2 | 2 | 2026-01-20 | NULL | NULL |
| 2 | 6 | 2026-03-08 | 2026-01-20 | 47 |
| 2 | 12 | 2026-05-21 | 2026-03-08 | 74 |
| 3 | 4 | 2026-02-14 | NULL | NULL |
| 3 | 8 | 2026-03-28 | 2026-02-14 | 42 |
| 3 | 14 | 2026-06-09 | 2026-03-28 | 73 |
+-------------+----------+------------+------------+-----------------+Aggregates as window functions: running totals
Any aggregate — SUM, AVG, COUNT, MIN, MAX — becomes a window function when you add OVER. Add ORDER BY inside and it accumulates:
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month,
revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total,
SUM(revenue) OVER () AS grand_total,
ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) AS pct_of_total
FROM monthly
ORDER BY month;+---------+---------+---------------+-------------+--------------+
| month | revenue | running_total | grand_total | pct_of_total |
+---------+---------+---------------+-------------+--------------+
| 2026-01 | 6600 | 6600 | 85050 | 7.8 |
| 2026-02 | 7000 | 13600 | 85050 | 8.2 |
| 2026-03 | 21400 | 35000 | 85050 | 25.2 |
| 2026-04 | 23450 | 58450 | 85050 | 27.6 |
| 2026-05 | 8500 | 66950 | 85050 | 10.0 |
| 2026-06 | 18100 | 85050 | 85050 | 21.3 |
+---------+---------+---------------+-------------+--------------+OVER (ORDER BY month)sums from the first row up to the current one: a running total.OVER ()— an empty window — is the whole result, so every row sees the grand total. Dividing by it gives the percentage of total in one pass, with no subquery.100.0 *makes the division decimal.revenue / SUM(…)on two integers would be integer division in SQLite and PostgreSQL and give 0.
Add PARTITION BY for "share within a group". Each product's share of its own category's revenue:
SELECT c.name AS category,
p.name AS product,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(100.0 * SUM(oi.quantity * oi.unit_price)
/ SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY c.name), 1) AS pct_of_category
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN products AS p ON p.product_id = oi.product_id
JOIN categories AS c ON c.category_id = p.category_id
WHERE o.status <> 'cancelled' AND c.name IN ('Books', 'Electronics')
GROUP BY c.name, p.name
ORDER BY category, revenue DESC;+-------------+---------------------------+---------+-----------------+
| category | product | revenue | pct_of_category |
+-------------+---------------------------+---------+-----------------+
| Books | Hands-On Machine Learning | 5000 | 58.1 |
| Books | Python Crash Course | 3600 | 41.9 |
| Electronics | 27-inch Monitor | 18500 | 58.6 |
| Electronics | Mechanical Keyboard | 8550 | 27.1 |
| Electronics | Wireless Mouse | 4500 | 14.3 |
+-------------+---------------------------+---------+-----------------+Notice SUM(SUM(…)) OVER (…). Window functions run after GROUP BY, so the inner SUM is the per-product group total and the outer one adds those totals across the partition. It reads oddly the first time, but it is correct and common.
Frames: which rows the function sees
A window has three parts: PARTITION BY (which rows belong together), ORDER BY (their order) and the frame (which of those rows count for the current row). You write the frame after ORDER BY:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- this row and the two before it
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- start of partition to here
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- the whole partition
RANGE BETWEEN 30 PRECEDING AND CURRENT ROW -- ORDER BY value within 30 of this one
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW -- this peer group and the one beforeMoving averages with ROWS
A three-month moving average smooths out a noisy series — the same idea as a smoothed loss curve when training a model:
WITH monthly AS (
SELECT strftime('%Y-%m', o.order_date) AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY month
)
SELECT month,
revenue,
ROUND(AVG(revenue) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS avg_3m,
COUNT(*) OVER (ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS months_in_avg
FROM monthly
ORDER BY month;+---------+---------+---------+---------------+
| month | revenue | avg_3m | months_in_avg |
+---------+---------+---------+---------------+
| 2026-01 | 6600 | 6600.0 | 1 |
| 2026-02 | 7000 | 6800.0 | 2 |
| 2026-03 | 21400 | 11667.0 | 3 |
| 2026-04 | 23450 | 17283.0 | 3 |
| 2026-05 | 8500 | 17783.0 | 3 |
| 2026-06 | 18100 | 16683.0 | 3 |
+---------+---------+---------+---------------+The first two rows average fewer than three months (see months_in_avg). Report them, drop them, or show them greyed out — but know they are there.
The default frame, and why ROWS and RANGE differ
When you write ORDER BY in a window and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; with no ORDER BY at all, the frame is the whole partition. RANGE treats rows with the same ORDER BY value (peers) as one step, so a tie is added all at once. Nusrat and Zahid both earn 120000:
SELECT name,
salary,
SUM(salary) OVER (ORDER BY salary) AS default_range,
SUM(salary) OVER (ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
SUM(salary) OVER (ORDER BY salary
GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS this_and_prev_group
FROM employees
ORDER BY salary, name;+-----------------+--------+---------------+---------+---------------------+
| name | salary | default_range | by_rows | this_and_prev_group |
+-----------------+--------+---------------+---------+---------------------+
| Shuvo Roy | 95000 | 95000 | 95000 | 95000 |
| Priya Sen | 110000 | 205000 | 205000 | 205000 |
| Nusrat Jahan | 120000 | 445000 | 325000 | 350000 |
| Zahid Hasan | 120000 | 445000 | 445000 | 350000 |
| Lamia Karim | 160000 | 605000 | 605000 | 400000 |
| Hasan Mahmud | 180000 | 785000 | 785000 | 340000 |
| Ayesha Siddiqua | 250000 | 1035000 | 1035000 | 430000 |
+-----------------+--------+---------------+---------+---------------------+| Frame unit | Counts in | Ties (peers) | Support |
|---|---|---|---|
ROWS | physical rows | split, in an arbitrary order unless you add a tie-breaker | SQLite, MySQL 8, PostgreSQL |
RANGE | distance in the ORDER BY value | always together | all three; value offsets (30 PRECEDING) need a single numeric or date ORDER BY, and SQLite 3.28+ / PostgreSQL 11+ |
GROUPS | groups of peers | always together | SQLite 3.28+, PostgreSQL 11+; not MySQL |
With by_rows, Nusrat's row includes only herself among the two 120000 earners, and which of them comes first is not guaranteed. For a deterministic running total, either keep RANGE or make the order unique (ORDER BY salary, employee_id) and use ROWS.
RANGE with a value offset: "the last 30 days"
RANGE shines when the frame is a span of values, such as time. For each order, count the orders placed in the 30 days up to and including its date, in each dialect:
SELECT order_id,
order_date,
COUNT(*) OVER (ORDER BY julianday(order_date)
RANGE BETWEEN 30 PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;+----------+------------+-----------------+
| order_id | order_date | orders_last_30d |
+----------+------------+-----------------+
| 1 | 2026-01-12 | 1 |
| 2 | 2026-01-20 | 2 |
| 3 | 2026-02-03 | 3 |
| 4 | 2026-02-14 | 3 |
| 5 | 2026-02-25 | 3 |
| 6 | 2026-03-08 | 3 |
| 7 | 2026-03-15 | 4 |
| 8 | 2026-03-28 | 3 |
+----------+------------+-----------------+-- PostgreSQL: dates order directly; the offset is an INTERVAL
SELECT order_id,
order_date,
COUNT(*) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;+----------+------------+-----------------+
| order_id | order_date | orders_last_30d |
+----------+------------+-----------------+
| 1 | 2026-01-12 | 1 |
| 2 | 2026-01-20 | 2 |
| 3 | 2026-02-03 | 3 |
| 4 | 2026-02-14 | 3 |
| 5 | 2026-02-25 | 3 |
| 6 | 2026-03-08 | 3 |
| 7 | 2026-03-15 | 4 |
| 8 | 2026-03-28 | 3 |
+----------+------------+-----------------+-- MySQL: the same idea, MySQL's INTERVAL syntax
SELECT order_id,
order_date,
COUNT(*) OVER (ORDER BY order_date
RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW) AS orders_last_30d
FROM orders
WHERE order_date < '2026-04-01'
ORDER BY order_date;All three return the same eight rows. SQLite stores dates as text, so the SQLite query orders by julianday(order_date), a number of days. Unlike ROWS BETWEEN 2 PRECEDING, this frame is right even when some days have no orders and others have several.
FIRST_VALUE, LAST_VALUE and the default-frame trap
FIRST_VALUE(x) and LAST_VALUE(x) return x from the first and last row of the frame (NTH_VALUE(x, n) from the n-th). Because the default frame stops at the current row, LAST_VALUE quietly returns the current row:
SELECT department,
name,
salary,
FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) AS top_earner,
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name) AS wrong_lowest,
LAST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC, name
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest
FROM employees
WHERE department IN ('Data', 'Engineering')
ORDER BY department, salary DESC, name;+-------------+--------------+--------+--------------+--------------+-------------+
| department | name | salary | top_earner | wrong_lowest | lowest |
+-------------+--------------+--------+--------------+--------------+-------------+
| Data | Lamia Karim | 160000 | Lamia Karim | Lamia Karim | Shuvo Roy |
| Data | Priya Sen | 110000 | Lamia Karim | Priya Sen | Shuvo Roy |
| Data | Shuvo Roy | 95000 | Lamia Karim | Shuvo Roy | Shuvo Roy |
| Engineering | Hasan Mahmud | 180000 | Hasan Mahmud | Hasan Mahmud | Zahid Hasan |
| Engineering | Nusrat Jahan | 120000 | Hasan Mahmud | Nusrat Jahan | Zahid Hasan |
| Engineering | Zahid Hasan | 120000 | Hasan Mahmud | Zahid Hasan | Zahid Hasan |
+-------------+--------------+--------+--------------+--------------+-------------+wrong_lowest is just each row's own name: the frame ended at the current row, so the current row was the last one. (Without the name tie-breaker, the frame would end at the last peer, so Nusrat's row could show Zahid — even more confusing.) Whenever you use LAST_VALUE or NTH_VALUE, give the full frame — or use FIRST_VALUE with the order reversed, which needs no frame at all.
Name the window once: the WINDOW clause
When several functions share a window, name it after HAVING (before ORDER BY) and refer to it. It works wherever window functions do: SQLite 3.25+, MySQL 8 and PostgreSQL:
SELECT o.customer_id,
o.order_id,
o.order_date,
SUM(oi.quantity * oi.unit_price) AS order_value,
ROW_NUMBER() OVER w AS order_no,
SUM(SUM(oi.quantity * oi.unit_price)) OVER w AS spend_to_date,
ROUND(AVG(SUM(oi.quantity * oi.unit_price)) OVER w, 0) AS avg_order_to_date
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status <> 'cancelled' AND o.customer_id IN (1, 2)
GROUP BY o.customer_id, o.order_id, o.order_date
WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY o.customer_id, o.order_date;+-------------+----------+------------+-------------+----------+---------------+-------------------+
| customer_id | order_id | order_date | order_value | order_no | spend_to_date | avg_order_to_date |
+-------------+----------+------------+-------------+----------+---------------+-------------------+
| 1 | 1 | 2026-01-12 | 2100 | 1 | 2100 | 2100.0 |
| 1 | 3 | 2026-02-03 | 3000 | 2 | 5100 | 2550.0 |
| 1 | 10 | 2026-04-19 | 18500 | 3 | 23600 | 7867.0 |
| 2 | 2 | 2026-01-20 | 4500 | 1 | 4500 | 4500.0 |
| 2 | 6 | 2026-03-08 | 15000 | 2 | 19500 | 9750.0 |
| 2 | 12 | 2026-05-21 | 3000 | 3 | 22500 | 7500.0 |
+-------------+----------+------------+-------------+----------+---------------+-------------------+This is exactly the shape of a feature table for a "will this customer order again?" model: each row knows only the past and present, never the future. Frames that end at CURRENT ROW are a built-in guard against target leakage, covered in depth in Preparing ML-Ready Data with SQL and pandas.
Where this shows up
- Dashboards: month-over-month change (
LAG), year-to-date (runningSUM), share of revenue (SUM() OVER ()). - Time-series and forecasting: lag features (
LAG(sales, 7)for "same day last week") and rolling means are standard inputs to forecasting models. - Product and LLM analytics: time between a user's sessions, tokens used so far this month per API key, a 7-day rolling average of evaluation scores to spot a regression.
Common mistakes
- Filtering on a window result in
WHERE.WHERE change < 0fails: windows are computed afterWHERE. Wrap the query in a CTE and filter outside:WITH t AS (SELECT …, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM monthly) SELECT * FROM t WHERE change < 0; - Forgetting
ORDER BYinOVER.SUM(revenue) OVER ()is the grand total on every row, not a running total. WriteOVER (ORDER BY month). LAST_VALUEwith the default frame. It returns the current row. AddROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.- A moving average over rows when periods are missing.
ROWS BETWEEN 2 PRECEDINGmeans "two rows", not "two months". Fill the calendar first, or useRANGEover a date. - Integer division in percentages.
100 * revenue / totalmay truncate; write100.0 * revenue / totalandROUNDit.
Try it yourself
- Easy: For every non-cancelled order of customers 1 and 3, show the date of that customer's next order (
NULLfor the last one). - Medium: List payments in
paid_onorder (tie-break onpayment_id) with a running total ofamountand each payment's percentage of all money received, rounded to 1 decimal. - Hard: For each department, show every employee's salary, the department's highest salary and the gap between the two, plus the department's average salary next to each row. Use one named window.
Answers
-- Easy
SELECT customer_id, order_id, order_date,
LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order
FROM orders
WHERE status <> 'cancelled' AND customer_id IN (1, 3)
ORDER BY customer_id, order_date;-- Medium
SELECT payment_id, paid_on, amount,
SUM(amount) OVER (ORDER BY paid_on, payment_id) AS running_total,
ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct_of_all
FROM payments
ORDER BY paid_on, payment_id;-- Hard
SELECT department, name, salary,
MAX(salary) OVER d AS dept_max,
MAX(salary) OVER d - salary AS gap_to_top,
ROUND(AVG(salary) OVER d, 0) AS dept_avg
FROM employees
WINDOW d AS (PARTITION BY department)
ORDER BY department, salary DESC, name;In the hard answer the window has no ORDER BY, so its frame is the whole partition and MAX and AVG see every employee of the department.
Summary
LAG/LEADread other rows by position; give them anORDER BY, and remember they count rows, not time.- Aggregates with
OVERgive running totals (ORDER BY), grand totals (OVER ()) and percentages of total without subqueries. - The frame decides what a function sees. The default with
ORDER BYisRANGE … CURRENT ROW, which includes peers and breaksLAST_VALUE. ROWScounts rows,RANGEmeasures values (great for "last 30 days"),GROUPScounts peer groups (not in MySQL).- Name shared windows with
WINDOW w AS (…); backward-looking frames make leak-free features.
Next: Analytics Patterns: Growth, Cohorts, Retention and Gaps combines CTEs and these window functions into the classic analysis queries: second-highest values, month-over-month growth, cohort retention tables and gaps-and-islands streaks.