Running Totals, LAG/LEAD & Gaps-and-Islands
Time-series analytics in SQL is cumulative sums, period-over-period deltas, and session islands. These are window problems first. Recursive CTEs are usually overkill for a linear sequence.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Consecutive days, sessions, and previous values
Prefer
Windows plus an island key
One ordered partition. LAG for the neighbor. day minus row_number for calendar islands. A cumulative sum of gap flags for sessions.
- Set-based and explicit.
- Needs a careful order and a dense key, or a generated calendar.
- Linear sequences stay off the recursive CTE.
Alternative
Self-join or a recursive walk
A self-join on “previous day” is familiar and can go quadratic. A recursive CTE can walk a line, and it is overkill.
- Inequality joins hide the tie rule.
- Recursion wants trees and graphs, not streaks.
- NULL lag values and duplicate days break naive island keys.
Overview
Time-series analytics in SQL is cumulative sums, period-over-period deltas, and session islands. These are window problems first.
Running totals
SELECT
account_id,
txn_at,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY txn_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS balance_after
FROM ledger;Explicit ROWS avoids RANGE peer surprises when timestamps collide. Why the default frame does that: window frames. Include id so two events at the same timestamp stay ordered.
LAG and LEAD
SELECT
user_id,
day,
events,
LAG(events, 1) OVER (PARTITION BY user_id ORDER BY day) AS prev_day,
events - LAG(events, 1) OVER (PARTITION BY user_id ORDER BY day) AS delta,
LEAD(day, 1) OVER (PARTITION BY user_id ORDER BY day) AS next_day
FROM user_daily;LAG(x, n, default)is n rows back. Day-over-day change, previous status.LEAD(x, n, default)is n rows forward. Next-event latency.FIRST_VALUE/LAST_VALUEread frame edges.LAST_VALUEoften needs the frame to run through unbounded following, because the default frame stops at the current row.
Forget the PARTITION BY and the lag becomes global. That is the wrong previous row.
Gaps-and-islands
Consecutive days of activity become sessions or streaks:
WITH ordered AS (
SELECT
user_id,
day,
day - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day))::int AS island_key
FROM user_active_days
)
SELECT user_id, MIN(day) AS island_start, MAX(day) AS island_end, COUNT(*) AS days
FROM ordered
GROUP BY user_id, island_key;Why it works: within a consecutive run, day - rn is constant. A gap changes the constant. day here is an integer day number (or a date you subtract as an integer). Duplicate days make rn move while day does not, so the key changes inside what a human calls one day. Deduplicate first.
Flow
- 1
1 Order events per entity
- next2 ROW_NUMBER in the partition
- 2
2 ROW_NUMBER in the partition
- next3 Key is day minus row number
- 3
3 Key is day minus row number
- next4 Group by entity and key
- 4
4 Group by entity and key
- next5 Emit start, end, and length
- 5
5 Emit start, end, and length
Lesson map
Running Totals, LAG/LEAD & Gaps-and-Islands
Time-series analytics in SQL is cumulative sums, period-over-period deltas, and session islands. These are window problems first. Recursive CTEs are usually overkill for a linear sequence.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1 Order events per entity"] b["2 ROW_NUMBER in the partition"] c["3 Key is day minus row number"] d["4 Group by entity and key"] a -->|1 Order events per entity| b b -->|2 ROW_NUMBER in the partition| c c -->|3 Key is day minus row number| d
Sessionize by a time gap:
CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
> INTERVAL '30 minutes'
THEN 1 ELSE 0 END AS new_island
-- then SUM(new_island) OVER (... ROWS UNBOUNDED PRECEDING) AS island_idThe cumulative sum of “this row starts an island” is the session id. The first row’s LAG is null. Treat null as a new island.
Missing calendar days are not islands by themselves. Build a generate_series spine, LEFT JOIN the facts, then window.
| Approach | Fit | Cost |
|---|---|---|
| Windows plus island key | Linear sequences | One ordered pass. Careful keys |
| Self-join on consecutiveness | Familiar | Quadratic risk. Messy ties |
| Recursive CTE | Graphs and trees | Overkill for a line of days |
Island key on days 1, 2, 3, 5, 6, 9
- 1
Number the rows
rn is 1 through 6 in day order. - 2
Subtract
Keys are 0, 0, 0, 1, 1, 3. - 3
Group the constant
Three islands: 1-3, 5-6, and 9. - 4
Duplicate day 2
The key is no longer constant. Dedupe before numbering.
Sandboxes
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
The first delta is null, matching LAG with no default. Money totals should use exact numeric types, not binary floats, once you leave this sketch.
Pitfalls
Days 1, 2, 3, 5, 6. Write the island key and the grouped start/end. Then flag a new session when ts - LAG(ts) exceeds 30 minutes and turn the flags into ids with a running sum.
Interview Q&A
Which frame for a running total?
Answer
Explicit ROWS from unbounded preceding through the current row. Add a unique tie-break to ORDER BY.
LAG vs a self-join to the previous row?
Answer
LAG is clearer and usually cheaper. The self-join has to invent “previous” with inequalities.
Explain gaps-and-islands.
Answer
A constant day - row_number marks a consecutive island. When the gap appears, the constant changes. Group by that key.
How do you sessionize by a time gap?
Answer
Flag a new island when LAG(ts) is farther than the threshold. The cumulative sum of those flags is the session id.
Why is LAST_VALUE weird?
Answer
The default frame ends at the current row, so LAST_VALUE is often the current value. Extend the frame to unbounded following.
What about missing calendar days?
Answer
generate_series a spine, LEFT JOIN the facts, then apply windows. Row offsets will not invent the missing days.
Can a recursive CTE do islands?
Answer
Yes, and you should still prefer windows for linear data. Recursion is for trees: CTEs.
What about numeric stability and NULLs?
Answer
Use exact types for money. LAG of the first row is null unless you pass a default. Nulls in the island expression split groups you did not mean to split.