SQL Practice 13/13

Capstone: rank customers, gaps included

The first capstone answered "who are our biggest customers?" using an INNER JOIN, which quietly drops anyone with zero orders - they don't show up as a zero, they just vanish. A real reporting query usually needs to show everyone, including the customers who haven't bought anything yet.

That means swapping the INNER JOIN for a LEFT JOIN, and handling the NULL it produces for a no-order customer's total with COALESCE. Add in a RANK() window function over the grouped totals and you've combined everything this module has covered into one query - the kind you'd actually be asked to write.

Same users and orders shape as the earlier join lessons, with one extra small order and one user - Ben - who has never ordered anything.

Your task: for each user, return their name, their total spent (as total_spent, using COALESCE so a user with no orders shows 0 instead of NULL), and their rank among all users by total spent (as rank, using RANK() OVER (ORDER BY ... DESC)) - ordered from highest spender to lowest.

You'll practice:

Using LEFT JOIN instead of INNER JOIN so no customer is silently dropped
Combining COALESCE, GROUP BY, and a window function in one query

-- Name, total spent (0 if no orders), and rank by total spent, highest first.
SELECT users.name, orders.amount FROM users JOIN orders ON orders.user_id = users.id;