Coding questions · Netflix
Aggregate D1 retention cohorts in SQL
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.
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).
Daily DAU, new-user cohorts, D1 retention, and revenue per DAU (exclude bots; dedupe events)
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:
- day (DATE)
- dau: distinct users with any event on that day
- new_users: users whose first-ever event_time falls on that day
- d1_retention_rate: among new_users on that day, the fraction who have any event on day + 1
- 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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(20) | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | signup_date | user_id |
|---|---|---|
| US | 2025-05-25 | u1 |
| US | 2025-05-26 | u2 |
| IN | 2025-05-26 | u3 |
| US | 2025-05-31 | u4 |
| US | 2025-06-01 | u5 |
| US | 2025-05-26 | bot_1 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(20) | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| product_id | VARCHAR(10) |
Sample data:
| event_time | event_type | product_id | user_id |
|---|---|---|---|
| 2025-05-26 09:00:00 | app_open | NULL | u1 |
| 2025-05-27 10:00:00 | app_open | NULL | u1 |
| 2025-05-26 12:00:00 | app_open | NULL | u2 |
| 2025-05-27 13:00:00 | view | P1 | u2 |
| 2025-05-28 12:00:00 | app_open | NULL | u2 |
| 2025-05-26 15:00:00 | app_open | NULL | u3 |
| 2025-05-27 16:00:00 | app_open | NULL | u3 |
| 2025-06-01 08:00:00 | app_open | NULL | u4 |
| 2025-06-01 09:00:00 | app_open | NULL | u5 |
| 2025-05-26 10:00:00 | app_open | NULL | bot_1 |
Table purchases
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(20) | NOT NULL |
| purchase_time | TIMESTAMP | NOT NULL |
| order_id | VARCHAR(20) | PRIMARY KEY, NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | order_id | purchase_time | user_id |
|---|---|---|---|
| 9.99 | o1 | 2025-05-27 11:00:00 | u1 |
| 4 | o2 | 2025-05-28 11:30:00 | u2 |
| 2.5 | o3 | 2025-05-29 14:00:00 | u3 |
| 1 | o4 | 2025-06-01 09:30:00 | u4 |
| 5 | o5 | 2025-06-02 10:00:00 | u5 |
| 100 | o6 | 2025-05-26 10:30:00 | bot_1 |
Expected output
One row per day from 2025-05-26 through 2025-06-01 with DAU, first-event cohort size, D1 retention rate for that cohort, and revenue per DAU (purchase revenue on that day divided by DAU). Days with no DAU still appear (with dau=0 and revenue_per_dau=NULL).
| d1_retention_rate | dau | day | new_users | revenue_per_dau |
|---|---|---|---|---|
| 1 | 3 | 2025-05-26 | 3 | 0 |
| NULL | 3 | 2025-05-27 | 0 | 3.33 |
| NULL | 1 | 2025-05-28 | 0 | 4 |
| NULL | 0 | 2025-05-29 | 0 | NULL |
| NULL | 0 | 2025-05-30 | 0 | NULL |
| NULL | 0 | 2025-05-31 | 0 | NULL |
| 0 | 2 | 2025-06-01 | 2 | 0.5 |
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.
- In PostgreSQL, use date + 1 for the recursive step and for checking day + 1 retention so the expression stays typed as DATE.