Cart Sizes

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

An analyst noticed the bulk-checkout invite list was counting refunds and zero-value adjustments in transactions as purchases, which pushed the wrong buyers over the bar. Rebuild it from purchases with a positive amount only: every user in users with more than twelve of them, showing their username, purchase count and total spent, biggest spenders first.

Power buyers. Big carts.

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

Under the invite-list story this is two filters living at two different grains. The positive-amount rule decides which ledger rows are purchases at all; the more-than-twelve bar decides which users survive once those purchases are counted. Anyone can write the join and the count. What separates candidates is putting each filter where its data lives. Every buyer on this data sits at twelve or fourteen ledger rows, so the bar is right on the edge: let a refund row into the count and a buyer can clear it on a purchase they gave back.

Build it in order

Step 1: Attribute each purchase to a buyer

Inner-join users to transactions on user_id. Users with no purchases can never reach thirteen, so the inner join loses nothing the answer needs.

Step 2: Throw out non-purchases before counting

WHERE t.total_amount > 0 is a row condition: it looks at one ledger row at a time, so it runs before grouping and keeps the zero-value row and the refund out of both COUNT(*) and SUM(t.total_amount).

Step 3: Count and total per user_id

GROUP BY u.user_id, u.username keys the group on the primary key and carries the display name along, so two people who share a username never merge.

Step 4: Cut on the count, then sort by spend

HAVING COUNT(*) > 12 filters groups after they are counted, and ORDER BY total_spent DESC puts the biggest spenders on top. The count decides who is on the list; the spend decides where they sit.

SELECT u.username,
       COUNT(*) AS purchase_count,
       SUM(t.total_amount) AS total_spent
FROM users AS u
JOIN transactions AS t
  ON t.user_id = u.user_id
WHERE t.total_amount > 0
GROUP BY u.user_id, u.username
HAVING COUNT(*) > 12
ORDER BY total_spent DESC

Row filter

WHERE sees one ledger row at a time. It can test total_amount, but no per-user count exists yet, so WHERE COUNT(*) > 12 is rejected outright.

Group filter

HAVING sees one user at a time, after counting. It can test COUNT(*), but by then the individual total_amount values are gone, so the refund rule cannot live here.

Common follow-up questions

• What if refunds should cancel a purchase rather than just be ignored? (Tests netting `total_amount` per user versus dropping rows.)
• How would you show only the top 10 spenders, and what decides who makes the cut on a tie at 10th? (Tests `LIMIT` and when a tie rule becomes necessary.)
• What changes if power buyers are judged only on the last twelve months? (Tests adding a second row condition on `transaction_date` without touching the group filter.)