New User Purchases

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

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.

What's this year's signup cohort worth so far?

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

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.

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'

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.

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.)