Coding questions · Netflix
Write SQL for DAU and first-purchase conversion
This question evaluates a candidate's practical SQL and data manipulation skills, including aggregations, distinct counts, joins, date-range handling, and identification of first-time purchases for conversion metrics.
Today is 2025-09-01. Using the schema and sample data below, write a single ANSI-SQL query that returns one row per day for the last 7 days (2025-08-26 to 2025-09-01 inclusive) with columns: day (DATE), dau (distinct users with a 'session' event that day), new_buyers (users whose first-ever order occurs that day), and conv_rate (new_buyers/dau rounded to 2 decimals; return 0.00 when dau=0). Rules: count only event_type='session' for DAU; a user's 'first-ever' order is the minimum order_date across all their orders; include dates with zero activity. Schema: users(user_id INT PRIMARY KEY, signup_date DATE); events(user_id INT, event_date DATE, event_type VARCHAR); orders(order_id INT PRIMARY KEY, user_id INT, order_date DATE, amount DECIMAL(10,2)). Sample tables: users
+---------+-------------+
| user_id | signup_date |
+---------+-------------+
| 1 | 2025-08-15 |
| 2 | 2025-08-30 |
| 3 | 2025-09-01 |
| 4 | 2025-08-10 |
+---------+-------------+
events
+---------+------------+------------+
| user_id | event_date | event_type |
+---------+------------+------------+
| 1 | 2025-08-26 | session |
| 1 | 2025-08-27 | session |
| 1 | 2025-09-01 | session |
| 2 | 2025-08-31 | session |
| 2 | 2025-09-01 | session |
| 3 | 2025-09-01 | session |
| 4 | 2025-08-26 | session |
| 4 | 2025-08-26 | click |
| 4 | 2025-08-27 | session |
+---------+------------+------------+
orders
+----------+---------+------------+--------+
| order_id | user_id | order_date | amount |
+----------+---------+------------+--------+
| 101 | 1 | 2025-08-27 | 50.00 |
| 102 | 2 | 2025-09-01 | 20.00 |
| 103 | 4 | 2025-08-26 | 15.00 |
| 104 | 1 | 2025-09-01 | 25.00 |
+----------+---------+------------+--------+
DAU and first-purchase conversion rate by day (last 7 days)
Assume the current date is 2025-06-01. Write a single PostgreSQL query that returns one row per day for the last 7 days (2025-05-26 to 2025-06-01 inclusive) with the following columns:
- day (DATE)
- dau: distinct users who have an event with event_type = 'session' on that day
- new_buyers: users whose first-ever order occurs on that day (i.e., their minimum order_date across all their orders equals that day)
- conv_rate: new_buyers / dau rounded to 2 decimals; return 0.00 when dau = 0
Additional rules:
- Count only event_type = 'session' for DAU (ignore other event types).
- Include dates with zero activity (still return a row for every day in the range).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-05-15 | 1 |
| 2025-05-30 | 2 |
| 2025-06-01 | 3 |
| 2025-05-10 | 4 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
Sample data:
| event_date | event_type | user_id |
|---|---|---|
| 2025-05-26 | session | 1 |
| 2025-05-27 | session | 1 |
| 2025-06-01 | session | 1 |
| 2025-05-31 | session | 2 |
| 2025-06-01 | session | 2 |
| 2025-06-01 | session | 3 |
| 2025-05-26 | session | 4 |
| 2025-05-26 | click | 4 |
| 2025-05-27 | session | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | order_date | order_id | user_id |
|---|---|---|---|
| 50 | 2025-05-27 | 101 | 1 |
| 20 | 2025-06-01 | 102 | 2 |
| 15 | 2025-05-26 | 103 | 4 |
| 25 | 2025-06-01 | 104 | 1 |
Expected output
7 rows (one per day from 2025-05-26 through 2025-06-01). Columns: day, dau, new_buyers, conv_rate.
| conv_rate | dau | day | new_buyers |
|---|---|---|---|
| 0.5 | 2 | 2025-05-26 | 1 |
| 0.5 | 2 | 2025-05-27 | 1 |
| 0 | 0 | 2025-05-28 | 0 |
| 0 | 0 | 2025-05-29 | 0 |
| 0 | 0 | 2025-05-30 | 0 |
| 0 | 1 | 2025-05-31 | 0 |
| 0.33 | 3 | 2025-06-01 | 1 |
Hints
- Build a date spine for 2025-05-26 through 2025-06-01 so you can return days with zero activity.
- Compute first-ever purchase date per user using MIN(order_date), then count how many users have first_order_date = day.
- Use COALESCE for missing counts and a CASE expression to avoid division by zero when dau = 0.