One Report, Everything at Once: An Analytical Capstone

Assembling a real monthly business report from the whole toolbox — CTEs to stage, date_trunc to bucket, window functions for running totals and month-over-month growth, FILTER for splits, and a per-month ranking — built up one layer at a time. Run against PostgreSQL 18.

Every chapter of this series taught one tool. This one uses them together, because that’s how real analysis works: a question worth answering rarely fits a single feature. We’ll build the report an analyst is actually asked for, over the bookshop’s real orders. It carries monthly revenue, with a running total, month-over-month growth, a returns rate, and the top author each month. The point isn’t any one clause; it’s the shape of assembling them. We’ll grow the query one layer at a time, run each layer, and only combine once each piece stands on its own. That incremental habit is the real lesson.

Start with the grain

Every analytical query begins with a decision about grain: one output row per what? Here it’s one row per month. date_trunc('month', order_date) snaps every date down to the first of its month, which turns 60,000 scattered orders into a handful of monthly buckets. Revenue lives in order_items (quantity times unit price), so we join orders to their line items and sum. Scoped to 2025 to keep the output readable:

SELECT date_trunc('month', o.order_date)::date AS month,
       round(sum(oi.quantity * oi.unit_price), 2) AS revenue,
       count(DISTINCT o.order_id) AS orders
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY 1
ORDER BY 1;
   month    |  revenue  | orders
------------+-----------+--------
 2025-01-01 | 284938.88 |   1882
 2025-02-01 | 251420.97 |   1670
 2025-03-01 | 279193.61 |   1855
 2025-04-01 | 280253.68 |   1865
 2025-05-01 | 278834.36 |   1862
 2025-06-01 | 271431.39 |   1784
 2025-07-01 | 273534.04 |   1838
 2025-08-01 | 289583.04 |   1927
 2025-09-01 | 243336.79 |   1641
(9 rows)

Nine months, nine rows. The count(DISTINCT o.order_id) is grain-awareness from the joins chapter. The join fans each order out to one row per line item, so a plain count(*) would count line items, not orders. This is the base of the report, and everything else layers on top of it.

Layer on the window calculations

The base gives per-month numbers in isolation. The questions an analyst actually asks are comparative: how does the total accumulate, and how does each month move against the one before? Both are window functions over the ordered months. A running total is sum(...) OVER (ORDER BY month), which sums every row up to and including the current one. Month-over-month growth needs the previous month’s value, which is lag(revenue) OVER (ORDER BY month), turned into a percentage.

Rather than repeat the base query, we stage it in a CTE and window over the result. This is the CTEs chapter earning its keep: name the intermediate result, then treat it as a table.

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS month,
         round(sum(oi.quantity * oi.unit_price), 2) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_date >= '2025-01-01'
  GROUP BY 1
)
SELECT month, revenue,
       sum(revenue) OVER (ORDER BY month) AS running_total,
       round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month))
             / lag(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month;

The first month’s mom_pct is blank, because lag has no prior row to reach and returns NULL, and arithmetic on NULL is NULL. That’s correct, not a bug: there is no month-over-month figure for the first month. The 100.0 * is the integer-division sidestep from the types chapter, keeping the ratio fractional.

Split each month with FILTER

Next the business wants a returns signal: how much of each month’s revenue came from orders that were later returned? That’s a conditional aggregate, and FILTER (WHERE ...) gives it to us in the same pass as the total, no self-join, no second query. We add one column to the monthly CTE:

round(sum(oi.quantity * oi.unit_price)
      FILTER (WHERE o.status = 'returned'), 2) AS returned_rev

Then 100.0 * returned_rev / revenue turns it into a percentage in the outer select. FILTER is the readable form of the portable sum(CASE WHEN status = 'returned' THEN ... END) trick, and it keeps the intent legible: same sum, narrowed to returned orders.

Rank the authors within each month

The last piece is the top author per month, which is a top-N-per-group with N=1. We compute each author’s revenue per month, then rank within the month with row_number() OVER (PARTITION BY month ORDER BY author_rev DESC) and keep rank 1. This is the ranking pattern from earlier, staged in its own pair of CTEs so it stays readable. Because it needs the author, this branch carries the extra joins to books and authors that the revenue base didn’t.

Keeping it separate matters: the revenue base is at order grain, but the author ranking is at (month, author) grain. Mixing two grains in one GROUP BY is how reports quietly double-count. Two CTEs, two grains, joined at the end on month.

The whole report

Now everything composes. Four CTEs stage the pieces: monthly revenue with its returns split, per-author monthly revenue, and the author ranking. The final SELECT joins them and adds the window calculations:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS month,
         round(sum(oi.quantity * oi.unit_price), 2) AS revenue,
         round(sum(oi.quantity * oi.unit_price)
               FILTER (WHERE o.status = 'returned'), 2) AS returned_rev
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.order_date >= '2025-01-01'
  GROUP BY 1
),
author_rev AS (
  SELECT date_trunc('month', o.order_date)::date AS month, a.name AS author,
         sum(oi.quantity * oi.unit_price) AS arev
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN books b   ON b.book_id = oi.book_id
  JOIN authors a ON a.author_id = b.author_id
  WHERE o.order_date >= '2025-01-01'
  GROUP BY 1, a.name
),
top_author AS (
  SELECT month, author,
         row_number() OVER (PARTITION BY month ORDER BY arev DESC) AS rn
  FROM author_rev
)
SELECT m.month,
       m.revenue,
       sum(m.revenue) OVER (ORDER BY m.month) AS running_total,
       round(100.0 * (m.revenue - lag(m.revenue) OVER (ORDER BY m.month))
             / lag(m.revenue) OVER (ORDER BY m.month), 1) AS mom_pct,
       round(100.0 * m.returned_rev / m.revenue, 1) AS pct_returned,
       t.author AS top_author
FROM monthly m
JOIN top_author t ON t.month = m.month AND t.rn = 1
ORDER BY m.month;
   month    |  revenue  | running_total | mom_pct | pct_returned | top_author
------------+-----------+---------------+---------+--------------+------------
 2025-01-01 | 284938.88 |     284938.88 |         |         18.5 | Author 38
 2025-02-01 | 251420.97 |     536359.85 |   -11.8 |         18.3 | Author 15
 2025-03-01 | 279193.61 |     815553.46 |    11.0 |         18.1 | Author 38
 2025-04-01 | 280253.68 |    1095807.14 |     0.4 |         17.7 | Author 15
 2025-05-01 | 278834.36 |    1374641.50 |    -0.5 |         15.6 | Author 15
 2025-06-01 | 271431.39 |    1646072.89 |    -2.7 |         15.8 | Author 15
 2025-07-01 | 273534.04 |    1919606.93 |     0.8 |         16.3 | Author 11
 2025-08-01 | 289583.04 |    2209189.97 |     5.9 |         15.9 | Author 15
 2025-09-01 | 243336.79 |    2452526.76 |    -16.0 |         16.7 | Author 38
(9 rows)

One statement, one pass, and a report you could paste into a monthly review. Read across a row: August peaked at 289,583, up 5.9% on July, with 15.9% of revenue on later-returned orders and Author 15 on top. Read down running_total and the year accumulates to 2.45M by September. Every number came from a tool this series covered, and nothing here loops or leaves the database.

How to read a query like this

The trick to writing one of these is that you don’t write it top to bottom in one go. You build the base, run it, confirm the grain, then wrap it in a CTE and add a layer, and run again. Each CTE is a checkpoint you can SELECT * from in isolation while debugging. When a number looks wrong, you bisect: which CTE first shows it? That layered, runnable structure is why CTEs matter beyond mere tidiness — they make a big query a stack of small, testable ones. If you take one working habit from this series, take that.

Final thoughts — closing Series 2

That’s the analytical toolbox. Series 1 taught you to retrieve data: select, filter, join, group. This series taught you to analyze it. CTEs to stage a query as readable layers, and recursion to walk hierarchies. Window functions to rank, to run totals across rows, and to look forward and back with lead/lag while keeping every row. date_trunc and the date functions to bucket time. CASE, FILTER, and grouping sets to split and pivot. Strings and regex to reshape text, jsonb to query documents in place, and arrays with LATERAL to bend the row model where you need to. Individually they’re features. Together, as this capstone showed, they’re a language for asking a business almost anything and getting the answer in one query.

There’s a question this series carefully avoided: how does the database actually run all this? When you write a nine-month report with four CTEs, three joins, and a stack of window functions, what does Postgres do with it? In what order, using which access paths — and why is one phrasing fast while an equivalent one crawls? That’s the subject of the next series, How Databases Run Your Query. We’ll read EXPLAIN output and learn what a query plan is telling you. We’ll see how indexes turn a full scan into a lookup, and when the planner ignores the one you built. And we’ll meet MVCC, the concurrency model that lets many people read and write the bookshop at once without stepping on each other. You’ve learned to describe the answer. Next, we learn how the machine finds it.

Comments