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.