OneMain Financial·Data Scientist·Technical Phone Screen·Senior

Senior

Jun 2026

Summary

Technical screen for a Data Scientist role at OneMain Financial. One meaty SQL question covering cohort analysis, retention math, and deduplication all at once. Felt like a take-home problem crammed into a live session.

Questions Asked (1)

Using PostgreSQL, write a single SQL query that computes D30 retention rate and 30-day ARPU for monthly signup cohorts. D30 retention counts users who had any event exactly on signup_date + 30 days (events before signup date are treated as data errors and excluded). 30-day ARPU is total payments within the first 30 days divided by cohort size. You also need to deduplicate events that are exact duplicates on (user_id, event_ts, event_name). Explain how your query handles duplicates and late events.

Product Analytics & MetricsData ModelingTechnical Trade-offs

Author's notes

This one took me a minute to untangle because there are actually three separate problems stacked on top of each other: the dedup, the retention window logic, and the ARPU window, and they all feed into one final aggregation.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and definitions, then outline a query structure using CTEs to deduplicate events, compute cohort sizes, calculate D30 retention, and compute 30-day ARPU. Emphasize how you handle duplicates and late events through deduplication and date filtering.

Pro tip: Mention that you would validate the query by checking edge cases like users with no events or payments, and discuss how you'd optimize performance with indexes on user_id and event_ts.

1. Clarify requirements and schema

Confirm table structures, definitions of signup_date, events, payments, and how to treat late events. Ensure understanding of D30 retention and ARPU calculations.

2. Deduplicate events

Use a CTE with DISTINCT or ROW_NUMBER() to remove exact duplicates on (user_id, event_ts, event_name), ensuring each event is counted once.

3. Compute cohort sizes

Aggregate users by signup month to get the total number of users in each cohort, which will be used as the denominator for both metrics.

4. Calculate D30 retention

Join deduplicated events to users, filter for events exactly on signup_date + 30 days, and count distinct users per cohort. Exclude events before signup_date as data errors.

5. Calculate 30-day ARPU

Sum payments within the first 30 days (signup_date to signup_date + 30 days) per cohort, then divide by cohort size to get ARPU.

Key Points to Mention

Use of CTEs for readability and modularity
Deduplication technique (e.g., ROW_NUMBER() or DISTINCT) and why it's necessary
Handling of late events: events before signup_date are excluded as data errors
Definition of D30 retention: exact match on signup_date + 30 days
Definition of 30-day ARPU: total payments in first 30 days divided by cohort size
Potential performance considerations: indexing on user_id, event_ts, and signup_date

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.