# New User Purchases > What's this year's signup cohort worth so far? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Growth wants to quantify how much revenue the 2026 signup cohort has generated. Sum all transaction spending for users who signed up during calendar year 2026 and return a single total. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### 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 1 of 1 rows: | total_revenue | | --- | | 46935.3 | ## Worked solution and explanation ### What this really is This is a cohort filter whose condition lives in a different table from the money, dressed up as a revenue total. The amounts sit in `transactions.total_amount`; cohort membership is `users.signup_date`, and neither table can answer alone. Anyone can write the `SUM`. The trap is the date: `transactions` already carries `transaction_date`, it is the partition key, and filtering on it feels free. Do that and you report **2026 calendar revenue, not cohort revenue**. Every older signup who bought something this year lands in the total, and the moment the calendar turns, the cohort's later spend falls out. ### Getting there #### Step 1: Locate the measure and the membership The figure to add up is `total_amount`, one row per sale in `transactions`. Who belongs to the cohort is decided by `signup_date`, which only `users` holds. The join is not optional: it is the only way to carry cohort membership onto the sales. #### Step 2: Join `transactions` to `users` on `user_id` `user_id` is unique in `users`, so each sale matches exactly one buyer and no amount is counted twice. That is the cardinality to confirm before trusting a `SUM` over a join. Cohort members who never bought anything have no sales to add, so an inner join loses nothing. #### Step 3: Filter on the signup year, not the sale year `strftime('%Y', u.signup_date) = '2026'` reads the year off the stored text. Some signups carry a clock time ('2026-09-09 14:53:00' in the sample), so a careless `signup_date <= '2026-12-31'` would drop a late-December signup with a time part. There is no `account_status` filter: a suspended account's past spend is still the cohort's revenue. #### Step 4: Collapse to one figure `SUM(t.total_amount)` with no grouping returns the single total. Voided sales add zero and refunds subtract, so this is net revenue as recorded. **Cohort revenue** ```sql SELECT SUM(t.total_amount) AS total_revenue FROM transactions t JOIN users u ON t.user_id = u.user_id WHERE strftime('%Y', u.signup_date) = '2026' ``` *Join for membership, filter on `signup_date`, sum `total_amount`.* **Sale-year filter** `WHERE strftime('%Y', t.transaction_date) = '2026'` sums every buyer's purchases dated 2026, no matter when they signed up. It runs fast and gives a plausible number for the wrong question. **Signup-year filter** `WHERE strftime('%Y', u.signup_date) = '2026'` fixes the population first, then counts all of its spend, whenever that spend landed. This is the cohort figure the question asks for. > **The cheap date is the wrong date** > > Candidates see that `transaction_date` is the partition key and reach for it to look performance-minded. Pruning partitions on the wrong column still gives the wrong answer. The interviewer will ask which population the number describes, and there is no good reply. > **Rule out fan-out before you sum** > > The tell is saying, before writing the `SUM`, that `user_id` is unique in `users` so the join cannot multiply any `total_amount`. Weaker candidates discover fan-out after the total looks too big; stronger ones rule it out up front. > **The filter lands on the small side** > > At 15M users and 100M sales partitioned by `transaction_date`, no partition can be pruned, so every sale is scanned once. The cohort side is small, so the engine filters `users` first and hash-joins against those ids. Wrapping `signup_date` in `strftime` blocks an index on it. The range `signup_date >= '2026-01-01' AND signup_date < '2027-01-01'` is index-friendly and still catches time parts. ## Common follow-up questions - How would you report the 2026 cohort's revenue per signup month? _(Adds a grouping key derived from `signup_date` without touching the join.)_ - Now count only each user's first 90 days after signup. What changes? _(Tests a per-user window: `transaction_date` relative to that user's own `signup_date`, not a fixed calendar range.)_ - Should refunds (a negative `total_amount`) count against the cohort? _(Tests whether you raise a business decision instead of silently filtering rows.)_ - What happens to a user whose `signup_date` is NULL? _(A NULL year never equals '2026', so the user drops out silently; tests NULL awareness in predicates.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/new_user_purchases) - [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.