CoursesBackend engineering from first request to production

Practice chapter 5 of 5

Build a SQL report that survives awkward data

Can a customer with zero paid orders appear in the report?

A join pairs rows by a condition. A left join also keeps each unmatched left row and supplies NULL values for its missing right side. NULL represents an unknown or absent value here. An ON condition controls which rows pair up. A WHERE condition subsequently filters the rows that survived the join.

Worked example

In PostgreSQL, run this read-only query. The VALUES lists act as tiny tables so there is nothing to install in the database.

WITH customers(id) AS (VALUES (1),(2),(3)), orders(id,owner,state) AS (VALUES (10,1,'paid'),(11,1,'open'),(12,2,'open')) SELECT c.id,o.id AS paid_order FROM customers c LEFT JOIN orders o ON o.owner=c.id AND o.state='paid' ORDER BY c.id;

Try it

Write down the three rows. Now move the paid condition from ON to a WHERE clause immediately before ORDER BY. Which customers disappear? Would adding OR o.id IS NULL to that WHERE clause recover both?

Compare your answer

The original returns (1,10), (2,NULL), (3,NULL). Filtering after joining returns only (1,10). Adding OR o.id IS NULL recovers customer 3. Customer 2 had a matching open order, so the join never produced a NULL-filled row for that customer. Filtering that order afterwards discards the customer's only row.

Source · PostgreSQL 17 · Joined tables and filtering

Why does a second tag double the reported revenue?

State what one input row represents before summing money. If one order can have several tags, a join produces one row per matching order and tag. SUM adds a value from each such row. DISTINCT on the amount treats equal amounts as the same value even when they came from different orders.

Worked example

Run WITH orders(id,cents) AS (VALUES (10,500),(11,500)), tags(order_id,tag) AS (VALUES (10,'red'),(10,'blue'),(11,'red')) SELECT COUNT(*) AS joined_rows, SUM(o.cents) AS revenue, SUM(DISTINCT o.cents) AS distinct_amounts FROM orders o JOIN tags t ON t.order_id=o.id; The numbers are 3, 1500 and 500. The contract says to count revenue from each tagged order once. The correct total is 1000 cents.

Try it

Keep the same two input tables. Replace the SELECT with a query that counts each tagged order once. Predict what happens if order 12 costs 700 cents and has no tags. Then add a second blue tag on a different order. Which changes should affect revenue?

Compare your answer

Use SELECT SUM(o.cents) FROM orders o WHERE EXISTS (SELECT 1 FROM tags t WHERE t.order_id=o.id); The test asks a yes-or-no question once per order and preserves its single amount. An untagged order contributes nothing. An additional tag on an already tagged order leaves revenue unchanged. Adding the first tag to order 12 raises revenue to 1700. For an empty matching set SUM returns NULL. Use COALESCE around the sum if the contract requires zero.

Source · PostgreSQL 17 · Aggregate functions

Which payment took the balance below zero?

A grouped sum collapses a customer's ledger into one total. A window function can attach a running sum to each original entry. PARTITION BY starts a separate calculation per account. ORDER BY defines the posting sequence inside it. A window frame says which entries in that sequence contribute to the current sum. Use whole cents to represent the fixture's money.

Worked example

Here day is an integer day number. For entries on the same day the business contract says the smaller posting ID happened first. Run WITH ledger(acct,day,id,cents) AS (VALUES ('a',1,10,500),('a',2,20,-700),('a',2,21,300),('b',2,30,50)) SELECT acct,id,SUM(cents) OVER (PARTITION BY acct ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance FROM ledger ORDER BY acct,day,id;

Try it

Identify the overdrawn posting. Replace the window's ORDER BY day,id and its ROWS frame with just ORDER BY day, keeping the outer display order. Predict the two balances on day 2 for account a. Explain how a report could miss a real overdraft even with all ledger rows present.

Compare your answer

The explicit posting sequence gives account a balances of 500, -200 and 100 cents. Posting 20 caused the overdraft. Account b starts independently at 50. With just ORDER BY day the default frame includes all peers through the current day. Both day-2 entries for a show 100. The outer sort arranges the display after the balances have been calculated and cannot reconstruct the missing intermediate balance. A unique ID is a useful tie-breaker only if the business agrees that its order represents posting order.

Source · PostgreSQL 17 · Window functions and frames