A small report

The question first

The report to build: for every author, how many books they wrote, how many copies those books sold, and a one-word label to go with the number — busiest first, and authors with no sales included rather than lost.

That sentence contains the whole plan. Each author: one row per author, so the grouping is by author. How many books: a count from the books table. Copies: a sum, which lives in a third table that has to be joined. A label: CASE. Busiest first: ORDER BY on the total. Included rather than lost: a LEFT JOIN, because an author with no books has no sales to match.

Building it in pieces

The habit that makes a report like this easy is the same one this course has used throughout: write the list of pieces first, then assemble them. A CTE for the counts per author keeps the final SELECT short enough to read.

Watch the joins do their work: books to authors gives each book its author, sales to books gives each sale its title, and the grouping folds all of it back into one row per author. Every piece answers one question.

Run a CTE on its own before attaching it to anything. The clause is a complete query in parentheses, so it can be executed by itself to see the rows it produces; a summary at the wrong grain is much easier to spot in isolation than in the finished report.

Decide the grain first

Before writing a join, decide what one row of the answer means. "One row per author" is a grain; so is "one row per book" and "one row per author per year". Every other decision follows from it: the grouping, the counts, and which table the join must keep whole.

The grain is also the test of a report. If the answer has two rows for the same author, either the grain is finer than you said or a join multiplied the rows. Reading the first few lines is enough to catch it.

Write the grain as a sentence and keep it next to the query. It is the one comment that stays true when the query changes.

Fan-out, and the count that follows

Joining a parent to a child repeats the parent once per child. Authors to books repeats an author per book; books to sales repeats a book per sale. A count after both joins counts the deepest rows, not the authors — which is why a report that says "books" can quietly report sales.

COUNT(DISTINCT b.id) counts each parent once, however many children it has. SUM has the same problem from the other side: if a book row is repeated by a join, its price is added once per repetition. Aggregate the child table before joining it, or use a subquery per metric, when the fan-out is more than one level.

The safest layout is one CTE per grain: count books per author in one, sum sales per book in another, then join the two summaries on the key. Each summary is already at the grain the final SELECT needs.

Check one number a second way

The quickest way to trust a report is to compute one of its numbers a second way. Sum the copies for a single author by hand from the sales table, then compare that with the row the report gives for them. If the two disagree, the join is the suspect: a missing ON clause, a filter in the wrong place, or a fan-out that counted a child twice.

Pick the author with no sales for the second check. That row is where outer joins and COALESCE fail quietly, and a zero that should have been a blank — or the other way round — is easiest to spot on a row with nothing in it.

The report, top to bottom

WITH totals AS (
  SELECT a.id, a.name, COUNT(DISTINCT b.id) AS books, COALESCE(SUM(s.copies), 0) AS copies
  FROM authors a
  LEFT JOIN books b ON b.author_id = a.id
  LEFT JOIN sales s ON s.book_id = b.id
  GROUP BY a.id, a.name
)
SELECT name,
       books,
       copies,
       CASE
         WHEN copies > 100 THEN 'best seller'
         WHEN copies > 0 THEN 'on the shelf'
         ELSE 'no sales yet'
       END AS standing
FROM totals
ORDER BY copies DESC, name;

Try these next

• Add a metric for the average copies per book and keep the author with no sales.
• Change the grain to one row per book and rewrite the grouping.
• Filter to books published after 1970 without dropping authors who have none — put the condition in the ON clause.