Spend and Rank

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L5

Problem

Spenders tied on total spend share a leaderboard position, so the top five positions can hold more than five people. Using transactions and the usernames in users, list every spender at position 5 or better with their total spend and position, position 1 going to the biggest spender.

Five thrones at the top of the spending leaderboard.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

What this really is

This is a tie-aware leaderboard dressed up as an exec report. Summing total_amount per user is the easy half; the half that separates candidates is where the position comes from. ORDER BY total DESC LIMIT 5 is a row cap, not a leaderboard: it has no position to return, and the day two users tie at fifth, one of them disappears on the engine's whim. ROW_NUMBER() manufactures a position but hands two equal spenders different numbers, which is a ranking nobody can defend in a review.

Step 1: Sum spend at the user grain

Inner-join users to transactions on user_id and take SUM(t.total_amount) per u.username. The inner join is deliberate: a user with no transactions has no spend and no claim to a position. Refunds and voided sales stay in, because a negative total_amount is money that came back and should come off the total.

Step 2: Position the totals, not the transactions

RANK() OVER (ORDER BY SUM(t.total_amount) DESC) is evaluated after the GROUP BY, so the window sees one row per user and orders users by their summed spend. RANK() gives equal totals the same position and skips the slots they used, the way a sports table does.

Step 3: Cut by position in the outer query

A window result cannot be filtered in the SELECT that computes it, so the totals and positions live in the ranked CTE and the outer query keeps rnk <= 5. Filtering on the position instead of counting rows is what lets a tie at fifth return six people.

WITH ranked AS (
    SELECT u.username, SUM(t.total_amount) AS total, RANK() OVER (ORDER BY SUM(t.total_amount) DESC) AS rnk
    FROM users u
    JOIN transactions t ON u.user_id = t.user_id
    GROUP BY u.username
)
SELECT username, total, rnk
FROM ranked
WHERE rnk <= 5

A row cap

ORDER BY total DESC LIMIT 5 returns exactly five rows and no position. With two users tied at fifth, which one survives depends on how the engine happened to sort them.

A position threshold

RANK() and then WHERE rnk <= 5 returns everyone at position 5 or better, tied users together, with the position as a column of the answer.

Common follow-up questions

• How would you return the top five spenders within each `age_bucket` instead of overall? (Tests adding `PARTITION BY age_bucket` to the window while keeping the position filter outside it.)
• Usernames are not guaranteed unique (the table holds both 'Alice' and 'alice'). How would you make the grain safe and still return `username`? (Tests grouping on the key `user_id` and carrying the name along, rather than grouping on a label.)
• If the executives want positions with no gaps after a tie, what changes? (Tests the `RANK()` versus `DENSE_RANK()` distinction and its effect on who makes the cut.)