Big Spenders

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

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Refunds and zero-dollar rows in transactions are not purchases, and our loyalty tier only opens to customers with at least 13 real purchases. For each customer who qualifies, report their total spend and purchase count, biggest spenders first.

The whale list.

Tables

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

This is a two-level filter dressed up as a loyalty program. One rule is about rows: a refund or a zero-dollar line is not a purchase. The other is about people: a customer earns the tier with at least 13 real purchases. Anyone can write the GROUP BY. What separates candidates is putting each rule on the level it lives on: drop the bad rows before grouping, judge customers after. Skip the row cleanup and a refund quietly counts as a purchase, pushing someone over the bar who never earned it while dragging their spend total down.

Two filters, two levels

Step 1: Throw out the non-purchases

WHERE total_amount > 0 removes the negative refund row and the zero-dollar row before anything is counted. It has to happen first: both the purchase count and the spend should only ever see real purchases.

Step 2: Roll up per customer

GROUP BY user_id, emitting SUM(total_amount) AS lifetime_spend and COUNT(*) AS tx_count. total_amount is already the line total, so it is summed as-is, never multiplied by quantity.

Step 3: Judge the customer, not the row

HAVING COUNT(*) >= 13. A purchase count does not exist until rows are grouped, so this test cannot live in WHERE. It runs on the cleaned count, which is exactly why step 1 had to come first.

Step 4: Biggest spenders first

ORDER BY lifetime_spend DESC. The sort key is a returned column, so the grader checks the order by spend and nothing else.

SELECT user_id,
       SUM(total_amount) AS lifetime_spend,
       COUNT(*) AS tx_count
FROM transactions
WHERE total_amount > 0
GROUP BY user_id
HAVING COUNT(*) >= 13
ORDER BY lifetime_spend DESC

Count first, ask later

Grouping raw rows means COUNT(*) includes the refund and the zero-dollar row. A customer with 12 real purchases plus a refund reads as 13 and gets in, and the refund shaves their lifetime_spend.

Clean first, then count

WHERE total_amount > 0 runs before the rollup, so COUNT(*) and SUM(total_amount) both describe real purchases, and HAVING judges each customer on the honest number.

Where the interviewer goes next

Common follow-up questions

• Net refunds against spend instead of dropping them, but still count only positive rows as purchases. (Tests a conditional count such as `SUM(CASE WHEN total_amount > 0 THEN 1 ELSE 0 END)` beside a plain `SUM(total_amount)`.)
• Only count purchases from the last 90 days toward the bar. (Another row-level rule, so it joins the `WHERE` clause, not `HAVING`.)
• Add each customer's most recent purchase date. (Checks that `MAX(transaction_date)` slots in as one more aggregate per `user_id`.)
• Define the tier as the top 10% of customers by spend instead of a fixed purchase count. (Moves the bar from a literal to a relative cutoff computed over all customers.)