New vs Returning User Share

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Yammer

Problem

We count a user as new in the month of their very first event in event_data and as returning in every month they are active after that. Show what share of each month's active users were new and what share were returning, earliest month first.

Fresh faces versus familiar ones.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Cohort dashboards live on the new-vs-returning split, and the interviewer is checking whether you reach for a materialized anchor CTE instead of a correlated subquery that re-scans 300M rows per user. They also want to hear COUNT(DISTINCT user_id), not COUNT(*), when counting active users, because a user with 30 events in a month still counts once.

Break down the requirements

Step 1: Anchor each user to their first month

SELECT user_id, strftime('%Y-%m', MIN(event_timestamp)) FROM event_data GROUP BY user_id. One row per user. This is the global truth used by every downstream month.

Step 2: Build the per-month active set

SELECT DISTINCT user_id, strftime('%Y-%m', event_timestamp) AS month FROM event_data. DISTINCT collapses a user firing 400 events in March into a single (user, month) row. Without it, the ratios become event-share, not user-share.

Step 3: Join active to anchor

Inner join on user_id. Every active user has exactly one anchor row, so the join is one-to-one per (user, month). No fan-out.

Step 4: Conditional aggregate the labels

SUM(CASE WHEN ufm.first_month = ma.month THEN 1 ELSE 0 END) for new, < ma.month for returning. Divide by COUNT(*) over the joined set, which equals distinct active users for that month. CAST(... AS REAL) to force float division.

The solution

WITH user_first_month AS (
  SELECT
    user_id,
    strftime('%Y-%m', MIN(event_timestamp)) AS first_month
  FROM event_data
  GROUP BY user_id
),
monthly_active AS (
  SELECT DISTINCT
    user_id,
    strftime('%Y-%m', event_timestamp) AS month
  FROM event_data
)
SELECT
  ma.month,
  CAST(SUM(CASE WHEN ufm.first_month = ma.month THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS new_user_ratio,
  CAST(SUM(CASE WHEN ufm.first_month < ma.month THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS returning_user_ratio
FROM monthly_active ma
JOIN user_first_month ufm ON ma.user_id = ufm.user_id
GROUP BY ma.month
ORDER BY ma.month

Correlated, O(N x U)

WHERE NOT EXISTS (SELECT 1 FROM event_data e2 WHERE e2.user_id = e.user_id AND e2.event_timestamp < date_trunc('month', e.event_timestamp)). Correct, but the planner re-probes event_data per outer row. On 300M rows this is hours, not minutes.

Materialized CTE, O(N)

Compute MIN(event_timestamp) per user once, store U rows, hash-join. One pass to build, one pass to probe. Scales linearly with events, not events times users.

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would the query change if you wanted 'new' defined by `users.signup_date` instead of first event? (Tests whether the candidate sees that anchor source is a product decision, not a SQL detail, and that the JOIN target swaps from a derived CTE to a dimension table.)
• How would you incrementalize this so each new month doesn't re-scan all 300M rows? (Probes whether they'd persist `user_first_month` as a slowly changing table (insert-only, anchor never moves backward) and only re-aggregate the latest month.)
• What breaks if a user has events in March, then no events for a year, then events in next March? (Tests the difference between 'new' (first ever) and 'resurrected' (returning after a gap), a common product-analytics nuance the prompt collapses.)
• How would you add a third bucket for 'resurrected' users with a 90-day inactivity threshold? (Forces a LAG over per-user months and a date diff, exercising window functions on top of the same anchor-join shape.)
• The `new_user_ratio` in the first month of the dataset is always 1.0. Is that a bug? (Catches whether the candidate recognizes the left-censoring boundary: anyone whose true first event predates the dataset gets falsely tagged new in month one.)