Advanced Reporting Patterns with CTEs, Windows, JSON Construction, and Reusable Views

Learning outcomes

ServiceHub's API needs one reusable operations report: team-level closed-order metrics, regional rank, and a JSONB payload suitable for an API response. The first prototype is a single deeply nested statement. It works, but nobody can tell where row filtering ends, where aggregation happens, why ranks change, or whether a view guarantees cached results. This lesson turns the report into explicit relational layers.

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.

\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. Design the row shape one layer at a time

A CTE is useful here as a naming and reasoning boundary. PostgreSQL may inline eligible side-effect-free CTEs, so using CTEs for readability does not mean demanding materialization. Each layer should answer one question and expose only the columns needed by the next.

WITH closed_base AS (  SELECT region, team_code, work_order_id, cost, actual_minutes  FROM app.ch06_work_order  WHERE status='closed'), team_metrics AS (  SELECT region, team_code,         count(*) AS closed_orders,         sum(cost)::numeric(12,2) AS closed_cost,         round(avg(actual_minutes),1) AS avg_minutes  FROM closed_base  GROUP BY region, team_code), ranked AS (  SELECT *,         dense_rank() OVER (           PARTITION BY region           ORDER BY closed_cost DESC, team_code         ) AS regional_cost_rank  FROM team_metrics)SELECT *FROM rankedORDER BY region NULLS LAST, regional_cost_rank, team_code;

Inspect each CTE independently while developing. The contract is: closed_base contains only eligible work orders; team_metrics contains one row per region/team; ranked adds a window value without changing that grain.

3. Create a reusable view with explicit stable columns

An ordinary view stores a query definition, not a cached result set. Querying it runs a plan derived from the view query and the outer query. Use explicit output expressions and names instead of SELECT * so consumers have a deliberate interface.

CREATE OR REPLACE VIEW app.ch06_region_team_report ASWITH closed_base AS (  SELECT region, team_code, cost, actual_minutes  FROM app.ch06_work_order  WHERE status='closed'), team_metrics AS (  SELECT region,         team_code,         count(*)::bigint AS closed_orders,         sum(cost)::numeric(12,2) AS closed_cost,         round(avg(actual_minutes),1)::numeric AS avg_minutes  FROM closed_base  GROUP BY region, team_code)SELECT region,       team_code,       closed_orders,       closed_cost,       avg_minutes,       dense_rank() OVER (         PARTITION BY region         ORDER BY closed_cost DESC, team_code       )::bigint AS regional_cost_rankFROM team_metrics;\d+ app.ch06_region_team_reportSELECT region, team_code, closed_orders, closed_cost, avg_minutes, regional_cost_rankFROM app.ch06_region_team_reportORDER BY region NULLS LAST, regional_cost_rank, team_code;

The explicit casts make the view's public types intentional. CREATE OR REPLACE VIEW also has compatibility rules for existing columns; treat view schema changes as API migrations, not as an informal side effect of editing a SELECT list.

4. Build JSON after the relational grain is correct

JSON construction should be the final representation layer, not a substitute for relational modeling. If you join raw one-to-many detail tables immediately before jsonb_agg, duplicates can enter the payload. Aggregate to the intended grain first, then construct objects and order the JSON aggregate explicitly.

SELECT region,       jsonb_agg(         jsonb_build_object(           'team', team_code,           'closed_orders', closed_orders,           'closed_cost', closed_cost,           'avg_minutes', avg_minutes,           'regional_cost_rank', regional_cost_rank         )         ORDER BY regional_cost_rank, team_code       ) AS teamsFROM app.ch06_region_team_reportGROUP BY regionORDER BY region NULLS LAST;

The ORDER BY inside jsonb_agg defines array element order. The outer ORDER BY only orders region rows. PostgreSQL's jsonb object-key display order is not a business API ordering contract; consumers should treat JSON objects as mappings.

5. Failure analysis: aggregate after a multiplying join

Suppose a future report joins each work order to tags and events before grouping by team. If a work order has two tags and three events, the join can generate six rows and inflate counts/costs. JSON will faithfully serialize the wrong relational result. The repair is to define each one-to-many grain separately—preaggregate tags/events or use correlated/LATERAL subqueries—before combining them with the one-row-per-work-order layer.

SELECT region, team_code,       count(*) AS view_rows,       count(DISTINCT team_code) AS distinct_team_valuesFROM app.ch06_region_team_reportGROUP BY region, team_codeHAVING count(*) <> 1;

0 rows-- the view contract is exactly one row per (region, team_code),-- with NULL region treated as a legitimate grouped value.

6. View, materialized view, or application cache?

Do not materialize because a SQL statement “looks long.” Measure the real query, define freshness and recovery requirements, and consider indexing/base-model changes first.

7. Inspect plan evidence after proving the result

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT region, team_code, closed_orders, closed_cost, regional_cost_rankFROM app.ch06_region_team_reportWHERE closed_cost >= 200ORDER BY region NULLS LAST, regional_cost_rank, team_code;

Record actual input/output rows, scans, aggregate/window/sort nodes, buffer usage, temporary I/O, and total time. On this tiny lab the timings are educationally meaningless for capacity planning. The purpose is to connect report layers to observable executor work, not to benchmark hardware.

8. Hands-on extension: API-shaped report with summary metadata

WITH region_payload AS (  SELECT region,         sum(closed_orders) AS closed_orders,         sum(closed_cost)::numeric(12,2) AS closed_cost,         jsonb_agg(           jsonb_build_object(             'team', team_code,             'orders', closed_orders,             'cost', closed_cost,             'rank', regional_cost_rank           ) ORDER BY regional_cost_rank, team_code         ) AS teams  FROM app.ch06_region_team_report  GROUP BY region)SELECT jsonb_build_object(         'generated_from', 'servicehub_lab',         'regions', jsonb_agg(           jsonb_build_object(             'region', region,             'closed_orders', closed_orders,             'closed_cost', closed_cost,             'teams', teams           ) ORDER BY region NULLS LAST         )       ) AS reportFROM region_payload;

The example intentionally omits clock_timestamp() from the payload so repeated executions over unchanged data are byte-for-byte easier to compare. If an API needs generation time, add it knowingly and adjust deterministic test expectations.

9. Cleanup, acceptance checks, and production judgment

SELECT count(*) AS report_rows,       count(DISTINCT ROW(region,team_code)) AS distinct_grainFROM app.ch06_region_team_report;-- Optional cleanup; keep it if later local experiments use the view.DROP VIEW IF EXISTS app.ch06_region_team_report;

In production, a view's owner, security model, search_path behavior of referenced functions, grants, migration compatibility, and dependency lifecycle matter as much as readability. Report queries may also expose sensitive fields or aggregate tenant data across boundaries. Chapter 20 later treats authentication, authorization, row-level security, and hardening as system-wide design concerns.

10. Chapter summary and bridge

Chapter 06 moved from one-value aggregates through row-preserving windows, multi-level grouping, observable writes, and reusable reports. The recurring discipline is the same: define the row population and grain, make ordering and NULL behavior explicit, distinguish relational correctness from plan shape, expose state changes directly, and verify postconditions. Chapter 07 applies that discipline to PostgreSQL's Multi-Version Concurrency Control (MVCC), isolation levels, locks, deadlocks, and serialization.

Authoritative references

• PostgreSQL 18 — WITH Queries
• PostgreSQL 18 — Window Functions
• PostgreSQL 18 — JSON Functions and Operators
• PostgreSQL 18 — CREATE VIEW
• PostgreSQL 18 — Materialized Views
• PostgreSQL 18 — EXPLAIN