Top Spender

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

L4

Problem

The VIP team is assembling a shortlist of the platform's biggest spenders on well-reviewed products. Total each customer's spending on products rated four stars or better, and keep only those whose total tops the largest single order ever placed on the platform. Return the username and that total, biggest spenders first.

When your spending exceeds the priciest item on the shelf.

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

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

What this problem is really testing

Strip off the VIP costume and this is a two-scope comparison: a per-customer sum held against one number computed over the whole platform. Everyone writes the three-table join and the four-star filter. What separates candidates is keeping the two scopes apart. The rating cut decides which orders feed each total. The bar is the largest order across every row of transactions, and the test runs only once the sum exists. Test individual orders against the bar and the list comes back empty, because no order beats the largest order. Take the bar from four-star orders only and it drops: the biggest order on this data is for a product rated 3.0, so a filtered bar lets in customers who never cleared the real one.

Working through it

Step 1: Wire up the three tables

Join users to transactions on user_id, and transactions to products on product_id, so each order carries its buyer's username and its product's rating. Inner joins are right here: a customer with no four-star order has nothing to total and stays off the list. Orders with a missing product_id drop out too, since nobody can say how their product was rated.

Step 2: Filter orders, not customers

p.rating >= 4 is a fact about one order's product, so it goes in WHERE, before anything is summed. Products with a NULL rating fail the comparison and fall out. That is what you want: an unrated product is not well-reviewed.

Step 3: Sum per customer, then gate the total

Group on u.user_id and u.username, sum t.total_amount into total_spend, and only then compare: HAVING SUM(t.total_amount) > (SELECT MAX(total_amount) FROM transactions). The subquery reads the whole table, untouched by the rating filter, and returns one number. Grouping on the id rather than the name means two customers who share a name are never merged.

Step 4: Put the biggest spenders on top

ORDER BY total_spend DESC is the whole order contract. The grade checks that your rows descend by total_spend, and customers who tie may come back either way, so you need no tiebreaker.

The solution

SELECT u.username,
       SUM(t.total_amount) AS total_spend
FROM users u
JOIN transactions t ON u.user_id = t.user_id
JOIN products p ON t.product_id = p.product_id
WHERE p.rating >= 4
GROUP BY u.user_id, u.username
HAVING SUM(t.total_amount) > (SELECT MAX(total_amount) FROM transactions)
ORDER BY total_spend DESC

Bar over four-star orders

(SELECT MAX(t.total_amount) FROM transactions t JOIN products p ON t.product_id = p.product_id WHERE p.rating >= 4) skips the largest order because its product is rated below four, so it returns a smaller number and admits more customers.

Bar over every order

(SELECT MAX(total_amount) FROM transactions) reads every order, whatever it was for, so the bar is the platform's true largest order and only customers who beat it stay.

Common follow-up questions

• How would the query change if the bar were each customer's own largest order rather than the platform-wide one? (Tests whether they can turn the uncorrelated subquery into a per-customer comparison and see what it does to the plan.)
• What if refunds sit in `transactions` as negative `total_amount` rows? (Tests whether net spend is what the shortlist should measure, and whether refunds should also be excluded from the bar.)
• If the team wanted five-star purchases only, held against twice the largest order, which clauses move? (Probes the `WHERE` versus `HAVING` boundary: the rating cut is a row change, the spend bar is a group change.)