Running totals, LAG and LEAD
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Window functions can do more than rank. With an ordered window, SUM becomes a running total, AVG over the last few rows becomes a moving average, and LAG and LEAD let each row read its neighbour's value, which is how growth and gaps are measured.
SUM() OVER for running totals
A running total, or cumulative sum, is the sum of the current row and every row before it. SUM(amount) OVER (ORDER BY txn_id) gives it, in the order of txn_id.
What is the wallet balance after each transaction? Add the new amount to everything before it: a running total.
Example 1 · UPI wallet balance as a running total
DROP TABLE IF EXISTS upi_txns;
CREATE TABLE upi_txns ( txn_id INT PRIMARY KEY, txn_date DATE NOT NULL, details VARCHAR(40) NOT NULL, amount DECIMAL(9,2) NOT NULL -- + money in, - money out);
INSERT INTO upi_txns VALUES (1, '2025-07-01', 'Pocket money from home', 5000.00), (2, '2025-07-02', 'Book purchase', -120.00), (3, '2025-07-03', 'Metro card recharge', -500.00), (4, '2025-07-05', 'Hostel rent fee', -3200.00), (5, '2025-07-06', 'Refund from friend', 250.00), (6, '2025-07-08', 'Movie ticket', -180.00);
SELECT txn_id, txn_date, details, amount, SUM(amount) OVER (ORDER BY txn_id) AS balanceFROM upi_txnsORDER BY txn_id;+--------+------------+------------------------+----------+---------+ | txn_id | txn_date | details | amount | balance | +--------+------------+------------------------+----------+---------+ | 1 | 2025-07-01 | Pocket money from home | 5000.00 | 5000.00 | | 2 | 2025-07-02 | Book purchase | -120.00 | 4880.00 | | 3 | 2025-07-03 | Metro card recharge | -500.00 | 4380.00 | | 4 | 2025-07-05 | Hostel rent fee | -3200.00 | 1180.00 | | 5 | 2025-07-06 | Refund from friend | 250.00 | 1430.00 | | 6 | 2025-07-08 | Movie ticket | -180.00 | 1250.00 | +--------+------------+------------------------+----------+---------+
What this code does
- For each row,
SUM(amount) OVER (ORDER BY txn_id)adds this amount and all earlier ones. Row 4: 5000 − 120 − 500 − 3200 = 1180.00. txn_idis unique, so every row is its own step. Ordering bytxn_datewould also work here because no two transactions share a date; the frame section shows what happens when they do.- Money is stored as
DECIMAL, so the balance is exact to the paisa.
Example 2 · Cumulative revenue, and a running total per customer
-- Cumulative ShopLite revenue month by monthWITH monthly AS ( SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items 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 cumulativeFROM monthlyORDER BY month;
-- Each customer's own running spend (Asha and Ravi)WITH order_totals AS ( SELECT o.customer_id, o.order_id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total FROM orders o JOIN order_items 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)SELECT customer_id, order_date, total, SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_spendFROM order_totalsORDER BY customer_id, order_date;+---------+----------+------------+ | month | revenue | cumulative | +---------+----------+------------+ | 2025-01 | 22544.00 | 22544.00 | | 2025-02 | 66037.00 | 88581.00 | | 2025-03 | 72083.00 | 160664.00 | | 2025-04 | 24594.00 | 185258.00 | | 2025-05 | 6298.00 | 191556.00 | | 2025-06 | 4897.00 | 196453.00 | +---------+----------+------------+ +-------------+------------+----------+---------------+ | customer_id | order_date | total | running_spend | +-------------+------------+----------+---------------+ | 1 | 2025-01-05 | 20597.00 | 20597.00 | | 1 | 2025-02-02 | 43839.00 | 64436.00 | | 1 | 2025-04-12 | 4398.00 | 68834.00 | | 2 | 2025-01-18 | 1947.00 | 1947.00 | | 2 | 2025-03-15 | 1897.00 | 3844.00 | | 2 | 2025-06-01 | 4897.00 | 8741.00 | +-------------+------------+----------+---------------+
What this code does
- The first query adds a running total to the monthly CTE: by June, ShopLite has earned 196453.00 from orders that were not cancelled.
- In the second query
PARTITION BY customer_idrestarts the total for Ravi (customer 2), so his running spend starts again at his first order. - Summing inside a CTE first and then running the window over its rows keeps each step simple.
A UPI wallet's balance is a running total of its credits and debits. Without window functions you would need a correlated subquery that adds up all earlier rows, once for every row, which is slow and hard to read. Add PARTITION BY to restart the total for each group: a running spend per customer, or a running score per team. Without ORDER BY inside OVER, SUM(amount) OVER () is not a running total; it is the grand total on every row.
Predict the output
Predict the balance
The amounts in txn_id order are 5000, -120, -500, -3200, 250, -180.
Moving averages with a window frame
A window frame chooses which rows around the current row the function uses, such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for the current row and the two before it.
A window frame chooses which rows around the current row the function uses.
WITH t (id, d, amount) AS ( SELECT 1, '2025-07-01', 100 UNION ALL SELECT 2, '2025-07-02', 50 UNION ALL SELECT 3, '2025-07-02', 30 UNION ALL SELECT 4, '2025-07-03', 20)SELECT id, d, amount, SUM(amount) OVER (ORDER BY d) AS by_date, SUM(amount) OVER (ORDER BY d, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rowFROM tORDER BY id;+----+------------+--------+---------+--------+ | id | d | amount | by_date | by_row | +----+------------+--------+---------+--------+ | 1 | 2025-07-01 | 100 | 100 | 100 | | 2 | 2025-07-02 | 50 | 180 | 150 | | 3 | 2025-07-02 | 30 | 180 | 180 | | 4 | 2025-07-03 | 20 | 200 | 200 | +----+------------+--------+---------+--------+
Example 3 · 3-day moving average of daily orders
DROP TABLE IF EXISTS daily_orders;
CREATE TABLE daily_orders ( day DATE PRIMARY KEY, orders INT NOT NULL);
INSERT INTO daily_orders VALUES ('2025-08-11', 120), ('2025-08-12', 150), ('2025-08-13', 90), ('2025-08-14', 200), ('2025-08-15', 170), ('2025-08-16', 260), ('2025-08-17', 310);
SELECT day, DAYNAME(day) AS weekday, orders, ROUND(AVG(orders) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3_day, SUM(orders) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_3_dayFROM daily_ordersORDER BY day;+------------+-----------+--------+-----------+-----------+ | day | weekday | orders | avg_3_day | sum_3_day | +------------+-----------+--------+-----------+-----------+ | 2025-08-11 | Monday | 120 | 120.0 | 120 | | 2025-08-12 | Tuesday | 150 | 135.0 | 270 | | 2025-08-13 | Wednesday | 90 | 120.0 | 360 | | 2025-08-14 | Thursday | 200 | 146.7 | 440 | | 2025-08-15 | Friday | 170 | 153.3 | 460 | | 2025-08-16 | Saturday | 260 | 210.0 | 630 | | 2025-08-17 | Sunday | 310 | 246.7 | 740 | +------------+-----------+--------+-----------+-----------+
What this code does
- On 13 August the frame holds 11, 12 and 13 August: (120 + 150 + 90) ÷ 3 = 120.0. Each later row slides the frame forward by one day.
- The first two rows average only what exists: 120.0 alone, then (120 + 150) ÷ 2 = 135.0.
- The dip on 13 August (90) barely moves the moving average, while the weekend rise shows clearly. That smoothing is why dashboards use moving averages.
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the current row and the two rows before it, so AVG(orders) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) is a 3-day moving average. It smooths out one unusually good or bad day.
The first rows have fewer rows behind them, so they average what exists: day 1 averages one value, day 2 two values. Other frames work the same way: 1 PRECEDING AND 1 FOLLOWING is a centred average of three rows, and UNBOUNDED PRECEDING means from the first row. The frame also explains a running-total surprise. With only ORDER BY and no frame, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same ORDER BY value as one step. Two transactions on the same date then show the same, combined total, as by_date shows below. Order by a unique column (ORDER BY d, id) or use a ROWS frame to get one step per row.
Predict the output
Predict the moving average
Daily orders are 120, 150, 90, 200 for the first four days.
LAG and LEAD: compare with the previous row
LAG(col) returns the value of col from the previous row in the window's order, and LEAD(col) from the next row.
Example 4 · LAG: days since the customer's previous order
SELECT c.name, o.order_id, o.order_date, LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date) AS prev_order, DATEDIFF(o.order_date, LAG(o.order_date) OVER (PARTITION BY o.customer_id ORDER BY o.order_date)) AS gap_daysFROM orders oJOIN customers c ON c.customer_id = o.customer_idWHERE o.customer_id IN (1, 2, 4, 5)ORDER BY c.name, o.order_date;+--------------+----------+------------+------------+----------+ | name | order_id | order_date | prev_order | gap_days | +--------------+----------+------------+------------+----------+ | Asha Rao | 1 | 2025-01-05 | NULL | NULL | | Asha Rao | 3 | 2025-02-02 | 2025-01-05 | 28 | | Asha Rao | 10 | 2025-04-12 | 2025-02-02 | 69 | | Kabir Singh | 5 | 2025-02-21 | NULL | NULL | | Kabir Singh | 12 | 2025-05-02 | 2025-02-21 | 70 | | Priya Sharma | 6 | 2025-03-03 | NULL | NULL | | Priya Sharma | 14 | 2025-05-20 | 2025-03-03 | 78 | | Ravi Kumar | 2 | 2025-01-18 | NULL | NULL | | Ravi Kumar | 7 | 2025-03-15 | 2025-01-18 | 56 | | Ravi Kumar | 15 | 2025-06-01 | 2025-03-15 | 78 | +--------------+----------+------------+------------+----------+
What this code does
LAG(o.order_date)reads the previous order date of the same customer, because ofPARTITION BY o.customer_id.- Each customer's first order has no previous order, so
prev_orderandgap_daysareNULL, not 0. - Asha Rao came back after 28 days and then after 69. A store uses this gap to decide when to send a "we miss you" coupon.
Example 5 · LEAD: next station on a train route
DROP TABLE IF EXISTS route_stops;
CREATE TABLE route_stops ( train_no INT NOT NULL, stop_no TINYINT NOT NULL, station VARCHAR(30) NOT NULL, arrives TIME NULL, departs TIME NULL, PRIMARY KEY (train_no, stop_no));
-- Sample timetable for a day trainINSERT INTO route_stops VALUES (12007, 1, 'Chennai Central', NULL, '06:00:00'), (12007, 2, 'Katpadi', '07:23:00', '07:25:00'), (12007, 3, 'Bengaluru', '11:00:00', '11:05:00'), (12007, 4, 'Mandya', '12:08:00', '12:10:00'), (12007, 5, 'Mysuru', '13:00:00', NULL);
SELECT stop_no, station, departs, LEAD(station) OVER (ORDER BY stop_no) AS next_station, TIMEDIFF(LEAD(arrives) OVER (ORDER BY stop_no), departs) AS travel_timeFROM route_stopsWHERE train_no = 12007ORDER BY stop_no;+---------+-----------------+----------+--------------+-------------+ | stop_no | station | departs | next_station | travel_time | +---------+-----------------+----------+--------------+-------------+ | 1 | Chennai Central | 06:00:00 | Katpadi | 01:23:00 | | 2 | Katpadi | 07:25:00 | Bengaluru | 03:35:00 | | 3 | Bengaluru | 11:05:00 | Mandya | 01:03:00 | | 4 | Mandya | 12:10:00 | Mysuru | 00:50:00 | | 5 | Mysuru | NULL | NULL | NULL | +---------+-----------------+----------+--------------+-------------+
What this code does
LEAD(station)reads the station of the next stop, andLEAD(arrives)its arrival time.TIMEDIFFgives the time between leaving this station and reaching the next.- The last stop, Mysuru, has no next row, so
next_stationisNULL, and withdepartsalsoNULLthe travel time isNULLtoo. - With many trains in the table you would add
PARTITION BY train_no, so one train's last stop never looks at another train's first stop.
LAG and LEAD let a row compare itself with its neighbour: today's price with yesterday's, this order's date with the customer's last order, this station with the next one.
The first row has no previous row, so LAG returns NULL there; the last row has no next row for LEAD. Both take two optional arguments: LAG(col, 2) looks two rows back, and LAG(col, 1, 0) returns 0 instead of NULL when there is no such row. With PARTITION BY customer_id, LAG never reaches into another customer's orders: each customer's first order gets NULL. That is exactly what "days since this customer's previous order" needs.
Find the bug
Find the wrong line
Each LAG or LEAD query has one wrong line. Click it.
Days since the previous order of the same customer.
Round 1 of 2Month-on-month growth
Month-on-month growth compares each month with the one before: (this month − last month) ÷ last month × 100. With LAG(revenue) OVER (ORDER BY month) the previous month sits in the same row, so the formula is ordinary arithmetic.
Example 6 · Month-on-month revenue growth
WITH monthly AS ( SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.order_id WHERE o.status <> 'CANCELLED' GROUP BY month),with_prev AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM monthly)SELECT month, revenue, prev_revenue, revenue - prev_revenue AS change_amt, ROUND(100 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS growth_pctFROM with_prevORDER BY month;+---------+----------+--------------+------------+------------+ | month | revenue | prev_revenue | change_amt | growth_pct | +---------+----------+--------------+------------+------------+ | 2025-01 | 22544.00 | NULL | NULL | NULL | | 2025-02 | 66037.00 | 22544.00 | 43493.00 | 192.9 | | 2025-03 | 72083.00 | 66037.00 | 6046.00 | 9.2 | | 2025-04 | 24594.00 | 72083.00 | -47489.00 | -65.9 | | 2025-05 | 6298.00 | 24594.00 | -18296.00 | -74.4 | | 2025-06 | 4897.00 | 6298.00 | -1401.00 | -22.2 | +---------+----------+--------------+------------+------------+
What this code does
with_prevputs last month's revenue next to this month's. The finalSELECTis plain arithmetic on two columns.- February grew 192.9% over January; April fell 65.9% after the big laptop sales of March. January has no previous month, so its change is
NULL. NULLIF(prev_revenue, 0)guards against a month with zero revenue: dividing byNULLgivesNULLinstead of a misleading number.
Build it in steps with CTEs: monthly totals first, then a column with LAG, then the percentage. The first month has no previous month and shows NULL, which is correct: growth is unknown, not zero. If a previous month could be 0, divide by NULLIF(prev_revenue, 0) to get NULL instead of a meaningless result.
One trap: window functions run after WHERE. If you filter WHERE month >= '2025-03' in the same query, March's LAG finds no February row and returns NULL. Compute LAG in a CTE on all months, then filter in the outer query.
... OVER w ... WINDOW w AS (ORDER BY month). The WINDOW clause goes after HAVING and before ORDER BY.Next step
Check your understanding
12 quick questions