Top Users by Recent Spend

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

Asked in

Vanguard

Problem

A single big order says nothing about how someone usually shops, so the loyalty analyst drafting VIP picks from transactions only looks at users with at least two purchases dated within the last 30 days, counting the whole of the day 30 days back. Return the three users with the highest average spend per purchase over that window, with their purchase count and that average, highest first.

Big spenders in the last 30 days.

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

What this really is

This is a minimum-sample rule dressed up as a VIP list. Anyone can average spend per user and take the top three. What separates candidates is seeing that an average over one purchase is just that purchase, and in a 30-day window most users have only one. 'At least two purchases' is a fact about a user's whole group of rows, so no row filter can express it, and it has to apply before the cut. Skip it and one-off buyers with a single big order take every VIP slot. Apply it after the cut and the list comes back short.

Building it

Step 1: Anchor the window to a date, not a timestamp

CURRENT_DATE - INTERVAL '30 days' returns a timestamp at midnight. Compare a plain transaction_date against that and the first day of the window counts as earlier than the bound, so its purchases fall out. Wrapping it as DATE(CURRENT_DATE - INTERVAL '30 days') brings the bound back to a date, and the whole day stays in.

Step 2: Collapse to one row per user

Use GROUP BY user_id and emit COUNT(*) as purchases and AVG(total_amount) as avg_spend. Refund rows stay in the average, so it reflects what the user actually kept.

Step 3: Qualify the group, not the row

HAVING COUNT(*) >= 2 runs after the groups exist, which is the only point where 'how many purchases did this user make' has an answer. In WHERE the test does not even parse.

Step 4: Cut last

ORDER BY avg_spend DESC LIMIT 3 sees qualified users only. A one-purchase user never takes up a slot, so the next regular moves up.

SELECT user_id,
       COUNT(*) AS purchases,
       AVG(total_amount) AS avg_spend
FROM transactions
WHERE transaction_date >= DATE(CURRENT_DATE - INTERVAL '30 days')
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY avg_spend DESC
LIMIT 3

Qualify after the cut

Take the top three by avg_spend in a subquery, then drop rows with purchases < 2. One-off buyers dominate an average, so you often return one or two users, and the regulars just below never get considered.

Qualify before the cut

HAVING removes one-time buyers first, so LIMIT 3 chooses among regulars only. The list is three repeat customers whenever three exist.

Common follow-up questions

• How would you return every user tied at third place on `avg_spend`? (Replacing `LIMIT` with a rank so ties at the boundary are kept.)
• What if `transaction_date` were a full timestamp rather than a date? (Whether the start should be midnight 30 days ago or exactly 30 times 24 hours back, and how the end of today is bounded.)
• Should a refund count as one of the two purchases? (Counting only rows with positive `total_amount` with a filtered count, without changing the average.)