SQL Practice 25/26

Capstone: define "top customer" before you query it

Every earlier lesson in this module had one right answer and your job was to find the SQL that produced it. This one is different, and it's the most important trap in the whole ladder: two queries can both be flawless SQL and still answer different questions.

A stakeholder asks: "who's our #1 customer?" You open orders and see two customers. Ana placed one order worth 500. Luka placed five smaller orders - 20, 15, 18, 22, 25 - that add up to 100.

Run the number of orders per customer, and Luka wins, 5 orders to 1. Run total money spent per customer, and Ana wins, 500 to 100. Nobody wrote a bug. Both queries are correct SQL. They're just correct answers to two different questions that both sound like "who's our #1 customer?" in casual English.

This is why the task below doesn't say "top customer" and leave it there. It pins the definition down first: top customer means the one who generated the most total revenue. Once the question is that specific, there's only one right query - the ambiguity was never in the syntax, it was in the sentence before you ever opened an editor.

Your task: find the top customer by total revenue (sum of amount across their orders). Return their name and their total. The answer is Ana, 500.

You'll practice:

Recognizing when two aggregate queries (COUNT vs SUM) are both correct SQL for different questions
Treating a vague requirement as something to pin down, not something to guess at
Using ORDER BY ... LIMIT 1 to pick a single winner from a GROUP BY

-- "Who's our #1 customer?" This counts orders, not revenue - it picks the wrong customer. Fix it to rank by total revenue.
SELECT customer AS name, COUNT(*) AS total FROM orders GROUP BY customer ORDER BY total DESC LIMIT 1;