Write SQL for DAU and first-purchase conversion
Company: Netflix
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
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 |
+----------+---------+------------+--------+
Overview: 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.
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).
Tables
users(user_id INT, signup_date DATE)
events(user_id INT, event_date DATE, event_type VARCHAR(20))
orders(order_id INT, user_id INT, order_date DATE, amount DECIMAL(10,2))
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.