# Top Users by Recent Spend > Big spenders in the last 30 days. Canonical URL: 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. ## Tables ### transactions - transaction_id INTEGER - user_id INTEGER - product_id INTEGER - quantity INTEGER - total_amount REAL - transaction_date TEXT Sample rows (10 of 200): | transaction_id | user_id | product_id | quantity | total_amount | transaction_date | | --- | --- | --- | --- | --- | --- | | 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 | | 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 | | 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 | | 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 | | 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 | | 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 | | 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 | | 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 | | 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 | | 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 | ## Expected output First 3 of 3 rows: | user_id | purchases | avg_spend | | --- | --- | --- | | 779 | 2 | 1013.5049999999999 | | 100 | 2 | 919.2149999999999 | | 1458 | 2 | 905.7449999999999 | ## 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. **Repeat buyers, best average basket** ```sql 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. > **A sample of one always looks extreme** > > With one purchase, the average is the order itself, and the largest single orders sit far above anyone's mean of two. Candidates see a plausible top three, sorted correctly, and never check `purchases` behind each average. > **Midnight is later than the date** > > A date and the midnight timestamp of the same day look equal to a person, but a comparison does not treat them as equal. `transaction_date >= CURRENT_DATE - INTERVAL '30 days'` reads fine and quietly shortens the window by a day, which can turn a two-purchase user into a one-purchase user. > **Two filters, two moments** > > Strong candidates explain why the date test lives in `WHERE` and the purchase count lives in `HAVING`: one judges rows, the other judges users. That explanation, more than the syntax, shows they know when each clause runs. > **The window does the heavy lifting** > > Thirty of 365 daily partitions on `transaction_date` means roughly a twelfth of the 120M rows are scanned. Because the bound is a plain date on the partition column, pruning still applies, and the aggregate works on about 10M rows. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_users_by_recent_spend) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.