Mixpanel Data Scientist Interview Experience — Four Retention SQL Questions in a 45-Minute Screen

Straight to the useful stuff. My Mixpanel Data Scientist phone interview was a SQL screen, around 45 minutes, with someone from the DS team.

Table structures, restated as I understood them, roughly like this:
users: user_id, signup_date, plan_type, ...
events: user_id, event_ts, ...

There were four questions, each building on the previous one, all about retention analysis.

Q1: How many users had at least one event after signup?
Q2: D7 retention: How many users had an event on the seventh day after signup?
Q3: How do you define and calculate weekly retention rate?
Q4: Does retention differ between free and paid users?

The first two were warm-ups. Q3/Q4 tested the approach: how to define cohorts, what to use for the denominator, and whether to segment by plan_type first.

My solutions:

-- Q1: Users with at least one event after signup
SELECT COUNT(DISTINCT e.user_id) AS users_with_event
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE e.event_ts >= u.signup_date;
-- Q2: D7 retention (an event on day 7 after signup)
SELECT COUNT(DISTINCT e.user_id) AS d7_retained_users
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE DATE(e.event_ts) = DATE(u.signup_date) + INTERVAL 7 DAY;
-- Q3: Weekly retention rate (cohorts by signup week, first-week retention)
WITH cohort AS (
SELECT user_id,
DATE_TRUNC('week', signup_date) AS signup_week
FROM users
),
retained AS (
SELECT DISTINCT e.user_id
FROM events e
JOIN users u ON e.user_id = u.user_id
WHERE e.event_ts >= u.signup_date
AND e.event_ts < u.signup_date + INTERVAL 7 DAY
)
SELECT c.signup_week,
COUNT(DISTINCT c.user_id) AS cohort_size,
COUNT(DISTINCT r.user_id) AS retained_users,
ROUND(COUNT(DISTINCT r.user_id) * 1.0 / COUNT(DISTINCT c.user_id), 4) AS retention_rate
FROM cohort c
LEFT JOIN retained r ON c.user_id = r.user_id
GROUP BY 1
ORDER BY 1;
-- Q4: Retention segmented by plan_type
SELECT u.plan_type,
COUNT(DISTINCT u.user_id) AS users,
COUNT(DISTINCT r.user_id) AS retained_users,
ROUND(COUNT(DISTINCT r.user_id) * 1.0 / COUNT(DISTINCT u.user_id), 4) AS retention_rate
FROM users u
LEFT JOIN (
SELECT DISTINCT e.user_id
FROM events e
JOIN users u2 ON e.user_id = u2.user_id
WHERE e.event_ts >= u2.signup_date
AND e.event_ts < u2.signup_date + INTERVAL 7 DAY
) r ON u.user_id = r.user_id
GROUP BY u.plan_type;

The interviewer was pretty happy with my solutions, and the conversation went well overall. I've already scheduled the onsite. I'll share the Python part in a couple of days and come back with an update after the onsite.