Return every customer with spent_cents and spend_rank. spent_cents is the sum of qty * unit_cents across their lines, or 0 if they never ordered. Rank by spent_cents descending using RANK() so ties share a rank. Order the result by spend_rank, then display_name.
Requirements
Keep the supplied tables unchanged.
Return display_name, spent_cents, and spend_rank.
Include Alonzo with spent_cents 0.
Ada's spend is 2000 and Grace's is 400.
Use RANK() OVER (ORDER BY spent_cents DESC).
Order by spend_rank ascending, then display_name ascending.
Example
Input
Keep the shop, then rank customers including Alonzo at zero.
Ranking needs one spend number per customer first. The CTE groups after LEFT JOIN so Alonzo still appears; COALESCE turns a null SUM into 0. RANK() then numbers those totals without deleting rows—the window keeps Ada, Grace, and Alonzo visible. DENSE_RANK would also work for this seed because there is no tie; RANK is the required function so a future tie would skip the next number. ORDER BY spend_rank, display_name puts the leaderboard on screen. An INNER JOIN would drop Alonzo and lie about who is last.
Why this pattern matters
Leaderboards need both a total and a rank on the same grain. GROUP BY alone cannot print Alonzo as rank 3 with spend 0 if you started from order_lines.
Solution walkthrough
LEFT JOIN orders and lines from customers.
COALESCE the SUM to 0.
RANK() OVER (ORDER BY spent_cents DESC) in an outer query or the same SELECT after grouping in a CTE.
Order by rank then name.
Common mistakes to avoid
INNER JOIN dropping Alonzo.
ROW_NUMBER when the spec asked for RANK.
Ranking inside the join before grouping, which ranks line items.
What this exercise teaches
LEFT JOIN so non-buyers remain
Aggregate then window, or window after a spend subquery
Use RANK rather than collapsing with GROUP BY alone
A PRACTICAL PLAN
Work through Rank customers by spend without collapsing rows with intent.
Translate the contract.Turn the requirements into a short checklist before editing your-solution.sql.
Use the example as evidence.Predict the result for the supplied input, then add one boundary case such as an empty value, a limit, or unexpected input.
Review the implementation.Compare your choices against solution.sql only after a real attempt.
EXERCISE FAQ
Before you move on.
What does “Rank customers by spend without collapsing rows” teach?
This advanced SQL exercise focuses on LEFT JOIN so non-buyers remain, Aggregate then window, or window after a spend subquery, Use RANK rather than collapsing with GROUP BY alone. Its requirements define the exact behavior to implement before you write code.
How should I validate this SQL solution?
Start with the displayed example input and expected output, then test a boundary case suggested by the requirements. Open your-solution.sql and select Run code. The browser creates a fresh isolated SQLite database, executes the complete script, and compares its result rows with the expected report.
When should I open the reference solution?
Attempt Rank customers by spend without collapsing rows first. Then open the read-only solution file to compare the contract, edge-case handling, and implementation choices—not simply to copy the final code.
