Aggregate D1 retention cohorts in SQL
Company: Netflix
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Today is 2025-09-01. Using SQL (optionally outline a pandas approach too), compute daily engagement and D1 retention for the last 7 days (2025-08-26 through 2025-09-01). Exclude any user_id beginning with 'bot_'. Use the following schema and sample data.
Schema:
users(user_id STRING, signup_date DATE, country STRING)
events(user_id STRING, event_time TIMESTAMP, event_type STRING, product_id STRING)
purchases(user_id STRING, purchase_time TIMESTAMP, order_id STRING, amount DECIMAL(10,2))
Sample tables (UTC times):
users
| user_id | signup_date | country |
| u1 | 2025-08-25 | US |
| u2 | 2025-08-26 | US |
| u3 | 2025-08-26 | IN |
| u4 | 2025-08-31 | US |
| u5 | 2025-09-01 | US |
| bot_1 | 2025-08-26 | US |
events
| user_id | event_time | event_type | product_id |
| u1 | 2025-08-26 09:00:00 | app_open | NULL |
| u1 | 2025-08-27 10:00:00 | app_open | NULL |
| u2 | 2025-08-26 12:00:00 | app_open | NULL |
| u2 | 2025-08-27 13:00:00 | view | P1 |
| u2 | 2025-08-28 12:00:00 | app_open | NULL |
| u3 | 2025-08-26 15:00:00 | app_open | NULL |
| u3 | 2025-08-27 16:00:00 | app_open | NULL |
| u4 | 2025-09-01 08:00:00 | app_open | NULL |
| u5 | 2025-09-01 09:00:00 | app_open | NULL |
| bot_1 | 2025-08-26 10:00:00 | app_open | NULL |
purchases
| user_id | purchase_time | order_id | amount |
| u1 | 2025-08-27 11:00:00 | o1 | 9.99 |
| u2 | 2025-08-28 11:30:00 | o2 | 4.00 |
| u3 | 2025-08-29 14:00:00 | o3 | 2.50 |
| u4 | 2025-09-01 09:30:00 | o4 | 1.00 |
| u5 | 2025-09-02 10:00:00 | o5 | 5.00 |
| bot_1 | 2025-08-26 10:30:00 | o6 | 100.00 |
Task: Produce a single SQL query returning one row per day d with the following columns: (1) day (DATE), (2) dau: distinct users with any event on day d, (3) new_users: users whose first-ever event_time is on day d, (4) d1_retention_rate: among new_users on day d, fraction with any event on day d+1, (5) revenue_per_dau: total purchase amount on day d divided by dau. Notes: treat days in UTC; deduplicate exact-duplicate events via SELECT DISTINCT user_id, event_time, event_type, product_id before aggregating; exclude 'bot_%' users from all metrics. Provide the SQL and, briefly, how you would compute the same using pandas (high-level steps).
Overview: This question evaluates a candidate's competency in time-series cohort analysis and SQL-based data manipulation, specifically measuring DAU, new-user cohorts, D1 retention and revenue-per-user while handling deduplication and user filtering.
Write a PostgreSQL query to compute daily engagement and D1 retention for the UTC date window from 2025-05-26 through 2025-06-01, inclusive.
Return exactly one row per calendar day with these columns:
1. day (DATE)
2. dau: distinct users with any event on that day
3. new_users: users whose first-ever event_time falls on that day
4. d1_retention_rate: among new_users on that day, the fraction who have any event on day + 1
5. revenue_per_dau: total purchase amount on that day divided by dau
Rules:
- Exclude any user_id starting with 'bot_' from every metric.
- Treat timestamps as UTC and derive dates with DATE(event_time) and DATE(purchase_time).
- Deduplicate exact duplicate events before aggregating by using DISTINCT over user_id, event_time, event_type, and product_id.
- Include every day in the requested window, even when there is no activity.
- If new_users = 0, return NULL for d1_retention_rate.
- If dau = 0, return NULL for revenue_per_dau.
Deliverable: a single PostgreSQL query ordered by day.
Tables
users(user_id VARCHAR(20), signup_date DATE, country VARCHAR(2))
events(user_id VARCHAR(20), event_time TIMESTAMP, event_type VARCHAR(20), product_id VARCHAR(10))
purchases(user_id VARCHAR(20), purchase_time TIMESTAMP, order_id VARCHAR(20), amount DECIMAL(10,2))
Hints
- Build a date spine first so zero-activity days remain in the output.
- Deduplicate and bot-filter events before computing DAU, first-event cohorts, and D1 activity.