Write SQL for cohort retention and ARPU
Company: OneMain Financial
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using PostgreSQL, compute monthly signup cohort D30 retention and 30-day ARPU. Cohort month = date_trunc('month', signup_date). D30 retention = users with any event on the calendar day signup_date + 30 days (ignore events before signup_date as data errors). 30D ARPU = sum of payments with pay_ts < signup_date + interval '30 days' divided by cohort size. Deduplicate exact duplicate events by (user_id, event_ts, event_name). Schema:
users(user_id INT PRIMARY KEY, signup_date DATE, country TEXT)
events(user_id INT, event_ts DATE, event_name TEXT)
payments(user_id INT, pay_ts DATE, amount NUMERIC(10,2))
Sample data:
users
+---------+-------------+---------+
| user_id | signup_date | country |
+---------+-------------+---------+
| 1 | 2025-06-01 | US |
| 2 | 2025-06-15 | US |
| 3 | 2025-07-03 | CA |
| 4 | 2025-07-20 | US |
| 5 | 2025-07-31 | GB |
+---------+-------------+---------+
events
+---------+------------+------------+
| user_id | event_ts | event_name |
+---------+------------+------------+
| 1 | 2025-07-01 | login |
| 1 | 2025-07-01 | login | (duplicate)
| 2 | 2025-07-15 | purchase |
| 3 | 2025-08-02 | login |
| 4 | 2025-08-19 | browse |
| 5 | 2025-08-30 | login |
+---------+------------+------------+
payments
+---------+------------+--------+
| user_id | pay_ts | amount |
+---------+------------+--------+
| 1 | 2025-06-20 | 10.00 |
| 1 | 2025-07-10 | 20.00 |
| 2 | 2025-06-16 | 15.00 |
| 3 | 2025-07-20 | 5.00 |
| 5 | 2025-08-15 | 12.00 |
+---------+------------+--------+
Write a single SQL query returning: cohort_month, d30_retention_rate, arpu_30d. Explain how your query avoids duplicates and late events.
Overview: This question evaluates a candidate's ability to perform time-based cohort analysis, compute D30 retention and 30-day ARPU, deduplicate exact event duplicates, and handle late or erroneous events using SQL.
Read the full OneMain Financial Data Scientist interview experience this question came from
Using PostgreSQL, compute monthly signup cohort D30 retention and 30-day ARPU.
Definitions:
- Cohort month = date_trunc('month', signup_date).
- D30 retention: a user is retained if they have ANY event on the calendar day (signup_date + 30 days). Ignore events before signup_date (treat them as data errors).
- Deduplicate events by exact duplicates on (user_id, event_ts, event_name) before using them for retention.
- 30-day ARPU: for each cohort, sum payments where pay_ts < (signup_date + interval '30 days') (also ignore payments before signup_date as data errors), then divide by cohort size.
Write a single SQL query that returns:
- cohort_month
- d30_retention_rate
- arpu_30d
Return one row per cohort_month.
Tables
users(user_id INT, signup_date DATE, country TEXT)
events(user_id INT, event_ts DATE, event_name TEXT)
payments(user_id INT, pay_ts DATE, amount NUMERIC(10,2))
Hints
- Use date_trunc('month', signup_date) to build cohorts, then aggregate per cohort_month.
- Deduplicate events with SELECT DISTINCT on (user_id, event_ts, event_name) before computing retention.
Community answers
Answer by singhk
WITH cohort_sizes AS (
SELECT
date_trunc('month', signup_date)::date AS cohort_month,
user_id,
signup_date
FROM users
),
deduped_events AS (
-- Deduplicate exact duplicate events as requested
SELECT DISTINCT user_id, event_ts, event_name
FROM events
),
user_retention AS (
SELECT
c.cohort_month,
c.user_id,
-- Check if the user has an event exactly on signup_date + 30 days
MAX(CASE WHEN e.event_ts = c.signup_date + INTERVAL '30 days' THEN 1 ELSE 0 END) AS is_retained
FROM cohort_sizes c
LEFT JOIN deduped_events e
ON c.user_id = e.user_id
-- Ignore events before signup_date as data errors
AND e.event_ts >= c.signup_date
GROUP BY c.cohort_month, c.user_id
),
user_payments AS (
SELECT
c.cohort_month,
c.user_id,
COALESCE(SUM(p.amount), 0) AS total_payments_30d
FROM cohort_sizes c
LEFT JOIN payments p
ON c.user_id = p.user_id
-- Payments within 30 days and ignoring payments before signup_date
AND p.pay_ts >= c.signup_date
AND p.pay_ts < c.signup_date + INTERVAL '30 days'
GROUP BY c.cohort_month, c.user_id
)
SELECT
cs.cohort_month,
ROUND(SUM(ur.is_retained)::NUMERIC / COUNT(DISTINCT cs.user_id), 4) AS d30_retention_rate,
ROUND(SUM(up.total_payments_30d) / COUNT(DISTINCT cs.user_id), 2) AS arpu_30d
FROM cohort_sizes cs
JOIN user_retention ur ON cs.user_id = ur.user_id
JOIN user_payments up ON cs.user_id = up.user_id
GROUP BY cs.cohort_month
ORDER BY cs.cohort_month;