Windows II — frames & running

ROWS vs RANGE, default-frame peer ties, rolling windows over gapped dates, moving-average edges.

• ★ROWS counts rows, RANGE counts time

  ROWS 6 PRECEDING is the last 7 rows; a rolling 7-day metric needs RANGE INTERVAL '6 days' (or a date spine) — they agree only at one row per entity per day.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE txns(user_id INT, txn_date DATE, amount INT); INSERT INTO txns VALUES (42, DATE '2024-01-01',10),(42, DATE '2024-01-09',30),(42, DATE '2024-01-10',5);

  The trap

  SELECT txn_date, SUM(amount) OVER (PARTITION BY user_id ORDER BY txn_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling FROM txns ORDER BY txn_date;

  2024-01-01	10
  2024-01-09	40
  2024-01-10	45

  The fix

  SELECT txn_date, SUM(amount) OVER (PARTITION BY user_id ORDER BY txn_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS rolling FROM txns ORDER BY txn_date;

  2024-01-01	10
  2024-01-09	30
  2024-01-10	35

  wrong by $10, no error raised, and every calendar gap makes it worse.

• ★Default frame includes peer ties

  ORDER BY with no explicit frame defaults to RANGE ... CURRENT ROW, which includes tied peers — running totals jump on tied keys; explicit ROWS is the bar for running sums.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE scores(player VARCHAR, t INT, pts INT); INSERT INTO scores VALUES ('A',1,10),('A',1,20),('A',2,5);

  The trap

  SELECT t, SUM(pts) OVER (ORDER BY t) AS running FROM scores ORDER BY t, pts;

  1	30
  1	30
  2	35

  The fix

  SELECT t, SUM(pts) OVER (ORDER BY t, pts ROWS UNBOUNDED PRECEDING) AS running FROM scores ORDER BY t, pts;

  1	10
  1	30
  2	35

  The running total jumps straight to 30 on the first tied row — the default RANGE frame swallowed the peer sharing its timestamp; ROWS adds them one at a time.

• Rolling windows over gapped dates

  Rolling metrics over dates with gaps need a date spine or per-day pre-aggregation (bridge to P12).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sales(d DATE, amt INT); INSERT INTO sales VALUES (DATE '2027-05-01',10),(DATE '2027-05-02',20),(DATE '2027-05-05',30);

  The trap

  SELECT strftime(d, '%m-%d') AS day, SUM(amt) OVER (ORDER BY d ROWS 2 PRECEDING) AS roll FROM sales ORDER BY d;

  05-01	10
  05-02	30
  05-05	60

  The fix

  WITH spine AS (SELECT g::DATE AS d FROM generate_series(DATE '2027-05-01', DATE '2027-05-05', INTERVAL 1 DAY) AS t(g)), daily AS (SELECT sp.d, COALESCE(SUM(s.amt), 0) AS amt FROM spine sp LEFT JOIN sales s ON s.d = sp.d GROUP BY sp.d) SELECT strftime(d, '%m-%d') AS day, SUM(amt) OVER (ORDER BY d ROWS 2 PRECEDING) AS roll FROM daily ORDER BY d;

  05-01	10
  05-02	30
  05-03	30
  05-04	20
  05-05	30

  The trailing-3 total at May 5 read 60 (reaching back to May 1, four days away) because ROWS counts rows, not days, over a gapped log — a date spine plus per-day pre-aggregation makes ROWS-over-days mean days, and emits the quiet May 3–4 rows the ROWS version never did.

• Moving-average edges

  The first N-1 rows of a moving average are partial windows — declare or exclude them.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE visits(d INT, n INT); INSERT INTO visits VALUES (1,10),(2,20),(3,30),(4,40);

  The trap

  SELECT d, AVG(n) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg3 FROM visits ORDER BY d;

  1	10
  2	15
  3	20
  4	30

  The fix

  SELECT d, AVG(n) OVER w AS avg3, (COUNT(*) OVER w < 3) AS partial FROM visits WINDOW w AS (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ORDER BY d;

  1	10	true
  2	15	true
  3	20	false
  4	30	false

  The first rows of the 3-day average ran over partial windows — day 1 averaged one point, day 2 two — but the chart called every point a '3-day average'.

• Frame exclusion

  EXCLUDE CURRENT ROW / TIES refine which frame rows contribute (H-tier garnish).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE v(d INT, x INT); INSERT INTO v VALUES (1,10),(2,20),(3,30);

  The trap

  SELECT d, AVG(x) OVER (ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS peer_avg FROM v ORDER BY d;

  1	15
  2	20
  3	25

  The fix

  SELECT d, AVG(x) OVER (ORDER BY d ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) AS peer_avg FROM v ORDER BY d;

  1	20
  2	20
  3	20

  Each row's peer average included itself, pulling it toward normal — EXCLUDE CURRENT ROW trims the row out of its own frame, so the middle row's peers average a true 20, not a self-inflected 20.

• Running total resets by PARTITION BY

  A running total resets where PARTITION BY says — omit the partition and one entity's total runs straight into the next.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE laps(track VARCHAR, lap INT, riders INT); INSERT INTO laps VALUES ('east',1,64),('east',2,58),('west',1,58),('west',2,60);

  The trap

  SELECT track, lap, SUM(riders) OVER (ORDER BY track, lap) AS cumulative FROM laps ORDER BY track, lap;

  east	1	64
  east	2	122
  west	1	180
  west	2	240

  The fix

  SELECT track, lap, SUM(riders) OVER (PARTITION BY track ORDER BY lap) AS cumulative FROM laps ORDER BY track, lap;

  east	1	64
  east	2	122
  west	1	58
  west	2	118

  The running total never reset between lines — without PARTITION BY, west's first cumulative showed 180 (east's 122 plus its own 58), where partitioning restarts each line at its own first lap.

• LAST_VALUE and the default frame

  With ORDER BY and no frame clause, the window ends at the current row — so LAST_VALUE returns the current row's value, not the partition's last.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE runs(day INT, secs INT); INSERT INTO runs VALUES (1,50),(2,45),(3,60);

  The trap

  SELECT day, LAST_VALUE(secs) OVER (ORDER BY day) AS final FROM runs ORDER BY day;

  1	50
  2	45
  3	60

  The fix

  SELECT day, LAST_VALUE(secs) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS final FROM runs ORDER BY day;

  1	60
  2	60
  3	60

  Each day's 'season final' echoed that day's own time — with ORDER BY and no frame (the default), the window ends at the current row, so LAST_VALUE returns the current row, not the season's last; an explicit UNBOUNDED FOLLOWING frame fixes it.

• Named WINDOW reuse

  A WINDOW clause names a window definition once — every OVER can reuse it, so clones can't silently disagree.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE runs(g VARCHAR, d INT, x INT); INSERT INTO runs VALUES ('m',1,10),('m',2,20),('m',3,5);

  The trap

  SELECT d, SUM(x) OVER (PARTITION BY g ORDER BY d) AS run_sum, COUNT(*) OVER (PARTITION BY g) AS run_cnt FROM runs ORDER BY d;

  1	10	3
  2	30	3
  3	35	3

  The fix

  SELECT d, SUM(x) OVER w AS run_sum, COUNT(*) OVER w AS run_cnt FROM runs WINDOW w AS (PARTITION BY g ORDER BY d) ORDER BY d;

  1	10	1
  2	30	2
  3	35	3

  The copy-pasted running count quietly lost its ORDER BY, so run_cnt read the grand total 3 on every row instead of 1, 2, 3 — a named WINDOW defines the window once, so the two columns can't disagree.