Putting it together: a shop report

The schema, and the rules in it

Four tables with the relationships the chapter on constraints built: customers and products on their own, orders pointing at a customer, and order lines pointing at both an order and a product. The keys and foreign keys are not decoration — they are what makes the joins in the report mean something, because an order line with no product cannot exist.

The prices live on the products rather than on the order lines, which is a simplification worth noticing: a real shop copies the price onto the line at the moment of sale, so a later price change cannot rewrite history. That copy is one column, and forgetting it is how reports start disagreeing with invoices.

Three questions, three shapes

Revenue by month is a calendar left-joined to the orders — the shape from the dates chapter, so a quiet month appears as a zero. The product leaderboard is a CTE that sums the line totals, with window functions laid over the result: a rank, a share of the whole, and a running total down the list. The customer summary is one GROUP BY with a CASE band, the sort of table that ends up on someone's dashboard.

Read together, they are the whole course in three queries: join, aggregate, group, window, name a step, format at the end. No new syntax is introduced here — a capstone should be a reunion, not an exam.

Where this goes on a real server

A real deployment adds three things this sandbox skips: a user and a password, a backup schedule, and a connection pool between the database and the application. Everything else — the types, the constraints, the queries — transfers exactly as written, which is why the practice here is not a toy.

On a server the report would also run inside a transaction, so its several queries can share one view of the shop rather than seeing it change between them. PostgreSQL gives each statement a consistent snapshot by default; a transaction at repeatable read holds that snapshot across all of them. Without it, a payment posted between the revenue query and the customer query can make the two totals disagree.

The habit to keep is the one behind EXPLAIN: when a query is slow, ask the database what it is doing instead of guessing. PostgreSQL will tell you, in detail, every time.

The order every report follows

A report is assembled in a fixed order: sources and joins first, then WHERE on rows, then GROUP BY, then HAVING on groups, then window functions, then the SELECT list, then DISTINCT, ORDER BY and LIMIT. Every confusing error about an alias or a window in WHERE is really a question about that order.

Formatting belongs at the very end, in the outermost SELECT, after the numbers are final. A to_char applied before a sum turns money into text, and text does not add. The same goes for round: round once, when the answer is complete, rather than at every intermediate step.

A report is a claim about one row

Before writing a report, say what one row means: one order, one customer, one product per month. That is its grain. Every join either preserves the grain or multiplies it, and a join to two child tables multiplies it twice. A total that survives a fan-out is a coincidence, not a result.

The check is cheap: sum the report and sum the source table with an independent query. If the two disagree, the report has counted something twice or dropped something quietly. A report that reconciles to its source is worth more than one that merely looks plausible.

When a fan-out is unavoidable, count(DISTINCT o.id) counts the parent once while the money still multiplies — a repair for counting, not for summing.

Try these next

• State the grain of each of the three reports in one sentence, then check that no join changes it.
• Reconcile the monthly revenue with a direct sum(total) over the orders table.
• Add a returns table and decide whether revenue is gross or net — then make the query say which.

The shape of the three reports

-- Months with revenue, zeros included.
WITH months AS (
    SELECT generate_series(
        date '2026-06-01', date '2026-09-01', interval '1 month'
    ) AS month_start
)
SELECT to_char(m.month_start, 'Mon YYYY') AS month,
       count(o.id) AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM months m
LEFT JOIN orders o
       ON o.placed_on >= m.month_start
      AND o.placed_on < m.month_start + interval '1 month'
GROUP BY m.month_start
ORDER BY m.month_start;

-- Products: revenue, rank, share and a running total.
WITH product_revenue AS (
    SELECT p.name,
           sum(l.quantity * p.price) AS revenue
    FROM order_lines l
    JOIN products p ON p.id = l.product_id
    GROUP BY p.name
)
SELECT name,
       revenue,
       rank() OVER (ORDER BY revenue DESC) AS place,
       round(100.0 * revenue / sum(revenue) OVER (), 1) AS share,
       sum(revenue) OVER (ORDER BY revenue DESC) AS running
FROM product_revenue
ORDER BY place;