Part 2 — Querying › Chapter 14

Frames, running totals, gaps and islands

ROWS and RANGE are not the same thing. Plus LAG/LEAD and the classic interview problem, solved three ways.

No advert asked for this

No advert names it, but “find the consecutive runs” and “running balance” appear in most technical screens.

The frame: the part everyone skips

🧒 Simple

Line the rows up in order. The frame is how far you can see from where you are standing: everything behind you, or three rows either side, or the whole line. Change how far you can see and the same question gives a different answer.

🎓 Professional

Inside OVER (…), after PARTITION BY and ORDER BY, comes the frame: which rows of the ordered partition the function actually sees.

  • No ORDER BY → the frame is the whole partition. That is why SUM(x) OVER (PARTITION BY c) is a partition total.
  • With ORDER BY and no explicit frame → the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That gives a running total — which is why adding an ORDER BY silently changes a total into a running total, the most surprising behaviour in the whole feature.

ROWS and RANGE are not the same thing

⚠ Trap — the default is the slow, surprising one

ROWS counts physical rows. RANGE counts peer rows — every row with the same ORDER BY value is included together.

So with three orders on the same day, RANGE … CURRENT ROW includes all three in every one of their running totals; ROWS … CURRENT ROW includes one, two, three respectively. If your running total "jumps" on days with several rows, this is why. And RANGE is the default, and is generally slower. Write ROWS explicitly unless you specifically want peer semantics.

The default frame is RANGE, and on tied values it does something most people do not expect.
-- Three rows, all on the SAME date d, with amounts 10, 20, 30:
SUM(x) OVER (ORDER BY d)                            -- RANGE (default): 60, 60, 60
SUM(x) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)   -- ROWS:            10, 30, 60

-- A 7-day moving average, done right:
SELECT d, amount,                                   -- the raw day and value
       AVG(amount) OVER (ORDER BY d                 -- ordered by date…
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) -- …seven PHYSICAL rows, not seven days
           AS ma7                                    -- the moving average column
FROM daily;                                          -- one row per day, already aggregated
Why the two results differ
  • RANGE frames by value: every row whose d ties with the current one is a peer and is included. Three rows on the same date are all peers, so all three see the whole 60.
  • ROWS frames by position: exactly the rows before this one. That gives the running total people actually mean.
  • So a running total on a table with several rows per day silently jumps by a whole day at a time under the default. Write ROWS explicitly unless you specifically want peer semantics — it is also generally faster.
  • “7 rows” equals “7 days” only if the table has exactly one row per day and no gaps. If it can have gaps, join a date spine first (chapter 12) or use RANGE BETWEEN INTERVAL '6 days' PRECEDING … where the engine supports it.
FrameMeans
ROWS UNBOUNDED PRECEDINGStart of partition → here. Running total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWA 7-row sliding window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGNeighbours either side. Smoothing
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGHere → end. “How much is still to come”
RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROWA true time window, gaps and all

That last row is the one case where RANGE is clearly better than ROWS: “the last seven days” is not “the last seven rows” when some days have no data. PostgreSQL and Oracle support it; SQL Server does not.

LAG, LEAD and the value-at functions

The value-at functions: look backwards, forwards, and at the start of the frame.
SELECT d, amount,                                              -- the row's own values
       LAG(amount)      OVER (ORDER BY d)        AS prev_day,  -- the previous row; NULL at the first
       LEAD(amount)     OVER (ORDER BY d)        AS next_day,  -- the next row; NULL at the last
       amount - LAG(amount, 1, 0) OVER (ORDER BY d) AS delta,  -- 3rd argument = default, so no NULL
       FIRST_VALUE(amount) OVER (ORDER BY d                    -- the first value in the frame…
                     ROWS UNBOUNDED PRECEDING) AS first_ever   -- …with the frame spelled out
FROM daily;                                                    -- one row per day
The NULLs, and the frame trap
  • LAG(amount) is NULL on the first row of each partition — there is no previous row. Subtracting a NULL gives NULL, which is why delta would be empty on day one without the third argument.
  • LAG(amount, 1, 0) reads “one row back, and 0 if there is not one”. The default must be type-compatible with the column.
  • FIRST_VALUE and especially LAST_VALUE are frame-sensitive. With the default frame, LAST_VALUE returns the current row — almost never what was wanted. Spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when you want the partition's actual last value.
⚠ Trap — LAST_VALUE almost never does what you expect

With the default frame, "the last value" is the current row, so LAST_VALUE(x) OVER (ORDER BY d) just returns x. You must extend the frame: LAST_VALUE(x) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). In practice FIRST_VALUE over a reversed ORDER BY is easier to read and harder to get wrong.

Gaps and islands

Why date minus row number finds runs of consecutive days The distinct days are numbered in order. Inside a run of consecutive days the date and the row number increase together, so their difference does not change. At a gap the date jumps while the row number does not, so the difference changes. Grouping by that difference gives one group per run. DAY ROW_NUMBER DAY − ROW_NUMBER GROUP Mar 011Feb 28 Mar 022Feb 28 Mar 033Feb 28 island 1 three days ← gap: no orders on 04, 05 Mar 064Mar 02 Mar 075Mar 02 island 2 two days The third column is constant inside a run and changes at every gap. That is the entire trick.
Once you have seen the third column, the technique stops being something to memorise.
🧒 Simple

You have a list of days somebody came to work: 1, 2, 3, 7, 8, 12. The islands are the runs of consecutive days — 1–3, 7–8, 12 — and the gaps are the holes between them. The trick to finding them is beautiful: subtract each day's position in the list from the day itself. Inside a run, that difference never changes.

Gaps and islands: subtract the row number from the value, and every run collapses to one constant.
-- day:  1 2 3 7 8 12
-- rn:   1 2 3 4 5  6      ← the position in the ordered list
-- d-rn: 0 0 0 3 3  6      ← constant WITHIN each run. That constant is the group key.

WITH numbered AS (                                     -- step 1: number the days in order
  SELECT d, ROW_NUMBER() OVER (ORDER BY d) AS rn       -- 1, 2, 3 … with no gaps by definition
  FROM attendance                                      -- one row per attended day
),                                                     -- end of step 1
grouped AS (                                           -- step 2: compute the island key
  SELECT d, d - rn AS grp FROM numbered                -- integer days; date arithmetic otherwise
)                                                      -- end of step 2
SELECT MIN(d) AS island_start,                         -- the run's first day
       MAX(d) AS island_end,                           -- its last day
       COUNT(*) AS length                              -- and how long it was
FROM grouped                                           -- read the keyed rows
GROUP BY grp                                           -- one output row per island
ORDER BY island_start;                                 -- chronological
Why the subtraction works
  • Inside a consecutive run, the day and the row number both increase by exactly one, so their difference never changes. At a gap, the day jumps and the row number does not — so the difference jumps too, and a new group starts.
  • The value of grp is meaningless; only its constancy matters. Do not show it to a user.
  • With real dates, d - rn needs date arithmetic: d - rn * INTERVAL '1 day' in PostgreSQL, DATEADD(day, -rn, d) in SQL Server, julianday(d) - rn in SQLite.
  • This is the single most-asked “hard” SQL interview question. Recognising the shape is the whole skill.
🎓 The general version: a gap flag and a running sum

The subtraction trick only works for a fixed step. The portable technique — and the one to show in an interview — is to mark where a new group starts and then take a running sum of those marks. It works for sessions, status runs, anything.

Sessionisation: mark where a new run starts, then take a running sum of the marks.
WITH flagged AS (                                          -- step 1: is this event a new session?
  SELECT *,                                                -- keep every original column…
         CASE WHEN LAG(ended_at) OVER (PARTITION BY user_id  -- …plus a flag: the previous event…
                                       ORDER BY started_at)  -- …of the same user, in time order…
                   >= started_at - INTERVAL '30 minutes'     -- …ended within the timeout?
              THEN 0 ELSE 1 END AS is_new_session            -- 0 = continues, 1 = starts a session
  FROM events                                                -- the raw event stream
),                                                           -- end of step 1
sessions AS (                                                -- step 2: turn the marks into ids
  SELECT *, SUM(is_new_session) OVER (PARTITION BY user_id   -- a RUNNING sum of the flags…
                                      ORDER BY started_at    -- …in the same order…
                                      ROWS UNBOUNDED PRECEDING) -- …ROWS, so no peer surprises
                                      AS session_id          -- 1, 1, 1, 2, 2, 3 …
  FROM flagged                                               -- reads step 1 by name
)                                                            -- end of step 2
SELECT user_id, session_id,                                  -- now group by the derived id
       MIN(started_at), MAX(ended_at), COUNT(*)              -- when it began, ended, how many events
FROM sessions GROUP BY user_id, session_id;                  -- one row per session
The pattern, and where else it applies
  • “Mark the starts, then take a running sum” converts a boundary condition into a group key. It works for web sessions, status runs, price periods, machine uptime — anything where a run ends when some condition breaks.
  • LAG(ended_at) is NULL for a user's first event, so the comparison is UNKNOWN, so CASE falls to ELSE 1: the first event correctly starts a session. That is luck turned into design — check it deliberately rather than assuming it.
  • ROWS UNBOUNDED PRECEDING rather than the default RANGE: two events starting at the same instant would otherwise be peers and share a running sum, merging two sessions into one.
  • INTERVAL '30 minutes' is PostgreSQL and Oracle. SQL Server writes DATEADD(minute, -30, started_at).
Your turn: the same shape, at a month grain.
-- EXERCISE. Find every customer's longest run of consecutive months in which
-- they placed at least one order.
--
-- It is gaps-and-islands with a month grain: truncate placed_at to the month,
-- take the DISTINCT months per customer, number them, and subtract.
-- Try it before reading the worked answer in course/SOLUTIONS.md.
Hints, in the order you will need them
  • Months are not integers, so “subtract the row number” needs a month-sized unit: in SQLite, strftime('%Y', m) * 12 + strftime('%m', m) turns a month into a countable integer.
  • DISTINCT matters: a customer with four orders in March must contribute one March, or the numbering is wrong before you start.
  • “Longest” means a second aggregation over the islands — MAX(length) per customer, or ROW_NUMBER() ordered by length and filtered to 1 if you also want the dates.
  1. No ORDER BY in OVER means the frame is the whole partition — a total, not a running total.
  2. Adding ORDER BY silently changes the default frame to “start of partition → current row”.
  3. ROWS counts physical rows; RANGE includes all peers with the same ordering value.
  4. RANGE is the default and is usually not what you meant. Write ROWS.
  5. RANGE with an INTERVAL is the one place it wins: a true time window across missing days.
  6. LAST_VALUE needs an explicit frame to the end of the partition, or it returns the current row.
  7. Islands of consecutive integers: group by value − ROW_NUMBER().
  8. The general form: flag where a new group starts, then take a running SUM of the flag as the group id.

Interview questions this chapter answers

Difference between ROWS and RANGE?

ROWS counts physical rows; RANGE includes every peer row sharing the current row's ORDER BY value. With duplicate ordering values they give different answers, and RANGE — the default — is also usually slower.

Compute a running total.

SUM(amount) OVER (PARTITION BY account ORDER BY d ROWS UNBOUNDED PRECEDING). The explicit ROWS matters: with the default RANGE, several rows on the same date all get the same day-end total.

Find runs of consecutive days.

Gaps and islands. Number the rows in date order and group by date − row_number, which is constant inside a run. For irregular steps, flag each row where the gap from the previous exceeds the threshold and take a running sum of that flag as a group id.