Netflix·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

Netflix Data Scientist technical screen, one meaty SQL question that covered DAU, cohort logic, and edge cases all at once. Felt like a reasonable challenge but the zero-activity dates requirement was the part that tripped me up most.

Questions Asked (1)

Write a single ANSI-SQL query returning one row per day for the last 7 days with columns for daily active users (session events only), new buyers (users whose very first order falls on that day), and conversion rate (new buyers divided by DAU, rounded to 2 decimals, returning 0.00 when DAU is zero). Dates with zero activity must still appear.

Product Analytics & MetricsData Modeling

Author's notes

The 'first-ever order' part is straightforward once you think MIN(order_date) grouped by user, but I initially forgot I needed to join that back against the date spine and almost counted repeat buyers.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by generating a complete date spine for the last 7 days using a recursive CTE or a calendar table, then left join aggregated daily metrics to it. Compute DAU from session events, new buyers from first-order dates, and finally calculate conversion rate with proper zero handling and rounding.

Pro tip: Explicitly state your assumptions about the database (e.g., date functions, session event definition) and mention that you'd validate the query against edge cases like zero DAU days and timezone boundaries—this shows production-level thinking.

1. Generate date spine

Create a list of the last 7 days (including today) using a recursive CTE or a calendar table to ensure all dates appear, even with no activity.

2. Aggregate DAU

Count distinct users from session events per day, filtering to the last 7 days, and group by date.

3. Identify new buyers

Find each user's first order date (using MIN(order_date) per user), then count users whose first order falls on each of the last 7 days.

4. Join and compute conversion rate

Left join the date spine with DAU and new buyers aggregates, then calculate conversion rate as new_buyers / NULLIF(dau, 0), rounded to 2 decimals, defaulting to 0.00 when DAU is zero.

5. Finalize output

Select the date, DAU, new buyers, and conversion rate columns, ensuring proper ordering by date and handling any NULLs from the left joins.

Key Points to Mention

Use of a date spine (recursive CTE or calendar table) to guarantee all 7 days appear.
Definition of DAU: distinct users with session events on that day.
New buyers: users whose first-ever order date matches the day.
Conversion rate calculation with NULLIF or CASE to avoid division by zero, and ROUND(..., 2).
Handling of NULLs from left joins (e.g., COALESCE to 0 for counts).
Assumptions about date functions (e.g., CURRENT_DATE, DATE_SUB) and timezone considerations.

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