Chapter 06 · Advanced SQL: Aggregates, Windows, GROUPING SETS, and MERGE
Window Partitions, Frames, Ranking, Running Metrics, and Gaps-and-Islands
Build PostgreSQL window calculations with explicit partitions, deterministic ordering, ROWS/RANGE/GROUPS frames, ranking, lag/lead, running and moving metrics, frame-aware first/last values, and gaps-and-islands analysis.
Learning outcomes
ServiceHub wants each work order shown beside its team rank,
running regional cost, previous order duration, moving average,
and the latest value in the region. Unlike an ordinary
aggregate, a window calculation keeps every input row. That
flexibility creates a frequent source of bugs: developers
specify ORDER BY in OVER(...) but
never define the frame, then assume
last_value means “last row in the partition.”
Distinguish a window partition, window ordering, peer group, and frame.
Use row_number, rank,
dense_rank, lag, and
lead with deterministic tie-breaking where
needed.
Compare ROWS, RANGE, and
GROUPS frame behavior instead of treating them
as synonyms.
Build running totals and moving metrics with explicit frame boundaries.
Diagnose first_value/last_value
frame traps and solve a gaps-and-islands problem
reproducibly.
Window functions are evaluated after WHERE/GROUP BY/HAVING and ordinary aggregation. They can appear in the SELECT list and ORDER BY of that query level. If you need to filter on a window result, compute it in a subquery or CTE and filter in the outer query.
1. Chapter 06 lab bootstrap
All five lessons use one small, deterministic
ServiceHub dataset. Run this in the disposable
servicehub_lab database as
servicehub_owner or another role that owns the
app schema. The objects are intentionally prefixed
ch06_ so Chapter 06 can be reset without touching
earlier chapters.
The reset is intentionally explicit and does not use CASCADE. Verify the database and matching objects before executing it. If any ch06_* object contains valuable data, stop and use another disposable lab database.
\echo 'Verify target before recreating Chapter 06 objects'SELECT current_database(), current_user;\dt app.ch06_*DROP VIEW IF EXISTS app.ch06_region_team_report;DROP AGGREGATE IF EXISTS app.ch06_sum_squares(numeric);DROP FUNCTION IF EXISTS app.ch06_add_square(numeric, numeric);DROP TABLE IF EXISTS app.ch06_inventory_stage;DROP TABLE IF EXISTS app.ch06_inventory;DROP TABLE IF EXISTS app.ch06_daily_metric;DROP TABLE IF EXISTS app.ch06_work_order;CREATE TABLE app.ch06_work_order ( work_order_id integer PRIMARY KEY, customer_id integer NOT NULL, region text, team_code text NOT NULL, status text NOT NULL CHECK (status IN ('open','closed','cancelled')), priority smallint NOT NULL CHECK (priority BETWEEN 1 AND 4), opened_on date NOT NULL, closed_on date, actual_minutes integer CHECK (actual_minutes >= 0), cost numeric(10,2) NOT NULL CHECK (cost >= 0));INSERT INTO app.ch06_work_order VALUES(1, 1,'North', 'A','closed', 1,'2026-08-01','2026-08-01', 40,200.00),(2, 2,'North', 'A','closed', 2,'2026-08-01','2026-08-02', 90,150.00),(3, 3,'North', 'B','closed', 1,'2026-08-02','2026-08-02', 60,300.00),(4, 4,'South', 'B','open', 2,'2026-08-02',NULL, NULL,120.00),(5, 4,'South', 'B','closed', 3,'2026-08-03','2026-08-04',180,500.00),(6, 5,'South', 'C','closed', 1,'2026-08-03','2026-08-03', 30, 80.00),(7, 1,'Central','A','closed', 2,'2026-08-04','2026-08-05',120,250.00),(8, 6,'Central','C','open', 1,'2026-08-04',NULL, NULL,110.00),(9, 7,NULL, 'C','closed', 2,'2026-08-05','2026-08-05', 75,130.00),(10,2,'North', 'A','closed', 3,'2026-08-05','2026-08-06',210,600.00),(11,8,'South', 'C','cancelled',4,'2026-08-06','2026-08-06',10, 0.00),(12,3,'Central', 'B','closed', 1,'2026-08-06','2026-08-07', 50,175.00);CREATE TABLE app.ch06_daily_metric ( metric_id integer PRIMARY KEY, metric_date date NOT NULL, region text, completed_count integer NOT NULL CHECK (completed_count >= 0), revenue numeric(10,2) NOT NULL CHECK (revenue >= 0), UNIQUE NULLS NOT DISTINCT (metric_date, region));INSERT INTO app.ch06_daily_metric VALUES(1,'2026-08-01','North', 1,200.00),(2,'2026-08-02','North', 2,450.00),(3,'2026-08-03','South', 1, 80.00),(4,'2026-08-04','South', 1,500.00),(5,'2026-08-05','Central', 1,250.00),(6,'2026-08-05',NULL, 1,130.00),(7,'2026-08-06','North', 1,600.00),(8,'2026-08-07','Central', 1,175.00);CREATE TABLE app.ch06_inventory ( sku text PRIMARY KEY, description text NOT NULL, on_hand integer NOT NULL CHECK (on_hand >= 0), reorder_point integer NOT NULL CHECK (reorder_point >= 0), active boolean NOT NULL DEFAULT true, updated_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch06_inventory (sku, description, on_hand, reorder_point) VALUES('A-100','Fuse kit',5,5),('B-200','Patch cable',20,10),('C-300','Pump seal',7,3);CREATE TABLE app.ch06_inventory_stage ( sku text PRIMARY KEY, description text NOT NULL, on_hand integer NOT NULL CHECK (on_hand >= 0), reorder_point integer NOT NULL CHECK (reorder_point >= 0));INSERT INTO app.ch06_inventory_stage VALUES('A-100','Fuse kit',8,5),('B-200','Patch cable',0,10),('D-400','Sensor battery',12,4);
The seed contains nine closed work orders, two open work orders,
one cancelled work order, a real NULL region,
repeated dates, and a small inventory snapshot. Those details
create observable edge cases for aggregate NULL handling,
peer-aware window frames, subtotal NULLs, and DML
synchronization.
2. Partition, order, peers, frame
A partition is the set of rows visible to one
window calculation, created by PARTITION BY. Window
ordering arranges rows inside a partition. Rows
equal on the window's ORDER BY keys are peers.
The frame is the subset of the partition
supplied to frame-sensitive functions for the current row.
| Concept | Example | Effect |
|---|---|---|
| Partition | PARTITION BY region |
Each region gets an independent calculation. |
| Order | ORDER BY cost DESC, work_order_id |
Defines deterministic sequence/ranking keys. |
| Peers | Rows tied on all window ORDER BY expressions. | rank/dense_rank treat peers together. |
| Frame |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
|
Limits frame-sensitive aggregate input around the current row. |
SELECT work_order_id, region, cost, row_number() OVER ( PARTITION BY region ORDER BY cost DESC, work_order_id ) AS row_pos, rank() OVER ( PARTITION BY region ORDER BY cost DESC ) AS cost_rank, dense_rank() OVER ( PARTITION BY region ORDER BY cost DESC ) AS dense_cost_rankFROM app.ch06_work_orderWHERE status='closed'ORDER BY region NULLS LAST, cost DESC, work_order_id;
row_number assigns a distinct sequence and
therefore needs deterministic tie-breakers when sequence
identity matters. rank and
dense_rank intentionally group peers; adding a
unique tiebreaker to those window ORDER BY clauses would remove
the peer relationship and change the business meaning.
3. lag/lead compare neighboring rows without collapsing them
SELECT work_order_id, region, closed_on, actual_minutes, lag(actual_minutes) OVER ( PARTITION BY region ORDER BY closed_on, work_order_id ) AS previous_minutes, actual_minutes - lag(actual_minutes) OVER ( PARTITION BY region ORDER BY closed_on, work_order_id ) AS delta_from_previousFROM app.ch06_work_orderWHERE status='closed'ORDER BY region NULLS LAST, closed_on, work_order_id;
lag and lead address rows at an offset
in the ordered partition. They do not need a frame
specification; their behavior is driven by partition and order.
If order keys are tied and no tie-breaker exists, “previous row”
is not a stable business concept.
4. ROWS, RANGE, and GROUPS answer different frame questions
ROWS counts physical ordered rows.
GROUPS counts peer groups. RANGE is
value/peer aware and, depending on its boundary form, can use an
offset relative to an ordering value. Do not substitute one mode
because the outputs happen to match on today's data.
SELECT work_order_id, opened_on, cost, sum(cost) OVER ( PARTITION BY region ORDER BY opened_on, work_order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS three_row_sum, sum(cost) OVER ( PARTITION BY region ORDER BY opened_on RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS through_date_sum, sum(cost) OVER ( PARTITION BY region ORDER BY opened_on GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW ) AS current_and_previous_date_groupFROM app.ch06_work_orderWHERE region='North'ORDER BY opened_on, work_order_id;id | opened_on | cost | three_row_sum | through_date_sum | current_and_previous_date_group---+-------------+--------+---------------+------------------+--------------------------------1 | 2026-08-01 | 200.00 | 200.00 | 350.00 | 350.002 | 2026-08-01 | 150.00 | 350.00 | 350.00 | 350.003 | 2026-08-02 | 300.00 | 650.00 | 650.00 | 650.0010 | 2026-08-05 | 600.00 | 1050.00 | 1250.00 | 900.00The RANGE running total includes all peers on the current date, so both August 1 rows see 350. The GROUPS frame at August 5 includes the current date group plus the immediately previous date group (August 2), yielding 900. The ROWS frame is explicitly a three-row moving window with a unique ordering key.
5. The last_value trap is a frame problem
With window ORDER BY, the default frame normally
ends at the current row's peer group. Therefore
last_value often returns the current/peer value
instead of the final value in the partition. If the business
question is “final closed duration in this region,” request a
partition-wide frame.
SELECT work_order_id, region, closed_on, actual_minutes, last_value(actual_minutes) OVER ( PARTITION BY region ORDER BY closed_on, work_order_id ) AS default_frame_last, last_value(actual_minutes) OVER ( PARTITION BY region ORDER BY closed_on, work_order_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS partition_lastFROM app.ch06_work_orderWHERE status='closed' AND region='North'ORDER BY closed_on, work_order_id;id | closed_on | minutes | default_frame_last | partition_last---+-------------+---------+--------------------+---------------1 | 2026-08-01 | 40 | 40 | 2102 | 2026-08-02 | 90 | 90 | 2103 | 2026-08-02 | 60 | 60 | 21010 | 2026-08-06 | 210 | 210 | 210When first_value, last_value, nth_value, or an aggregate-as-window function appears, review the frame explicitly. “The window is ordered” is not enough information to know which rows the function can see.
6. Running and moving metrics
SELECT metric_date, region, revenue, sum(revenue) OVER ( PARTITION BY region ORDER BY metric_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_revenue, round(avg(revenue) OVER ( PARTITION BY region ORDER BY metric_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ), 2) AS two_observation_avgFROM app.ch06_daily_metricORDER BY region NULLS LAST, metric_date;
ROWS is correct here because the metric table's primary key
guarantees one row per (metric_date, region),
including one NULL-region date under PostgreSQL's
NULLS NOT DISTINCT uniqueness. If multiple
observations per day became legal, the chosen frame would need
reconsideration.
7. Gaps and islands: derive groups from boundaries
An “island” is a consecutive sequence separated from another sequence by a gap. One robust pattern marks rows whose date is not exactly one day after the previous date, then cumulatively sums those boundary flags to form island identifiers.
WITH closed_days AS ( SELECT DISTINCT region, closed_on FROM app.ch06_work_order WHERE status='closed'), marked AS ( SELECT region, closed_on, CASE WHEN closed_on = lag(closed_on) OVER ( PARTITION BY region ORDER BY closed_on ) + 1 THEN 0 ELSE 1 END AS new_island FROM closed_days), numbered AS ( SELECT region, closed_on, sum(new_island) OVER ( PARTITION BY region ORDER BY closed_on ROWS UNBOUNDED PRECEDING ) AS island_id FROM marked)SELECT region, island_id, min(closed_on) AS island_start, max(closed_on) AS island_end, count(*) AS daysFROM numberedGROUP BY region, island_idORDER BY region NULLS LAST, island_start;
The DISTINCT in closed_days defines
the unit as calendar days rather than work orders. The ordering
key is deterministic for those distinct days. Change either
assumption and the island definition changes.
8. Hands-on lab and production judgment
WITH ranked AS ( SELECT work_order_id, region, cost, row_number() OVER ( PARTITION BY region ORDER BY cost DESC, work_order_id ) AS rn FROM app.ch06_work_order WHERE status='closed')SELECT work_order_id, region, costFROM rankedWHERE rn <= 2ORDER BY region NULLS LAST, rn;
Window functions can require sorts and large frames can retain
substantial state. Monitor actual sort methods, memory,
temporary I/O, row counts, and query latency when plan behavior
matters. Do not enlarge work_mem globally just
because one analytical query spills; Lesson 3 of Chapter 02
established that memory is multiplicative across operators and
sessions.
Check your understanding
- What is the difference between a partition and a frame?
- Why can row_number need an additional tie-breaker while rank intentionally may not?
- What makes RANGE and GROUPS peer-aware in different ways?
- Why does last_value often surprise users with the default frame?
- Why is a subquery/CTE needed when filtering by row_number in the same logical report?
Review the answers
The partition is the full row set assigned to a window; the frame is the current subset used by frame-sensitive calculations. row_number needs deterministic ordering when row identity matters, while rank may intentionally preserve peers. RANGE follows ordering values/peers, GROUPS counts peer groups. The default ordered frame ends at the current peer group, so last_value may be the current value. Window calculations occur after WHERE at that query level, so filter them in an outer query.
9. Bridge to multi-level reports
Windows preserve detail rows. The next lesson moves in the opposite direction: one statement will deliberately produce detail-group totals, parent totals, and a grand total using multiple grouping sets while retaining enough metadata to tell real NULL data from subtotal placeholders.