Capstone: a real report

Put every tool together in one query.

Bring it all together

Time to combine joins, aggregation, and ordering into a genuine business report — the kind you'd actually be asked for.

The brief: the shop wants a customer leaderboard. For every customer who has spent anything, show their name and total amount spent across all their orders (sum of quantity × unit_price), with the biggest spender first.

This needs: a join from customers through orders and order_items, a SUM over the line items, a GROUP BY the customer, and an ORDER BY the total. You've done every piece — now assemble it.

Money lives on order_items and names live on customers, so the report needs both hops. Every join in the query is one of these two arrows — follow them and the FROM clause writes itself.

The CTE computes sales per book and numbers them within each genre; the outer query keeps rnk = 1. Because science ties at 2 sold, the second ORDER BY key decides it — without that tiebreak the winner would change between runs.