← Netflix Interview Insights

Netflix·Data Scientist·Technical Phone Screen·Senior

Senior
Sep 2025Remote

Summary

Netflix DS interview, technical phone screen style. One big SQL problem covering DAU, new user cohorts, D1 retention, and revenue metrics all in a single query. The bot-exclusion and dedup requirements were the kind of gotchas that feel obvious in hindsight but trip you up in the moment.

Questions Asked (1)

Q1

Given three tables (users, events, purchases), write a single SQL query returning one row per day for a 7-day window with: daily active users, new users (first event on that day), D1 retention rate for those new users, and revenue per DAU. Exclude bot users and deduplicate exact-duplicate events before aggregating.

Product Analytics & MetricsData Modeling
Author's notes

This is a lot to pack into one query and I fumbled the D1 retention part first.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by building a clean, deduplicated events CTE that filters out bots and removes exact duplicates, then compute daily active users and new users per day. Use a self-join or window function to calculate D1 retention for new users, and join with purchases to compute revenue per DAU, ensuring all metrics are aggregated per day over the 7-day window.

Pro tip: Clarify the definition of 'new user' and 'D1 retention' upfront—e.g., new user as first event ever, and D1 retention as activity on the day after first event—and state any assumptions about time zones and bot identification, as these details matter for correctness.

1. Clean and deduplicate events

Create a CTE that filters out bot users (e.g., using a bot flag or user agent pattern) and removes exact duplicate events by selecting distinct rows or using ROW_NUMBER() over all columns.

2. Compute daily active users and new users

Aggregate the cleaned events to get DAU per day, and identify new users as those whose first event date falls on that day, using MIN(event_date) per user.

3. Calculate D1 retention for new users

For each day's new users, check if they have any event on the following day, then compute the retention rate as the proportion of new users who returned on D1.

4. Compute revenue per DAU

Join the cleaned events with purchases (after deduplication if needed) to sum revenue per day, then divide by the DAU for that day.

5. Combine metrics into final output

Use a calendar table or generate a series of dates for the 7-day window, left join all metrics, and ensure one row per day with the four required columns.

Key Points to Mention

  • Deduplication strategy: use DISTINCT or ROW_NUMBER() to remove exact duplicate events before aggregation.
  • Bot exclusion: filter based on a bot flag, user agent, or other heuristics, and mention the assumption.
  • Definition of new user: first event ever, and ensure it's within the 7-day window.
  • D1 retention calculation: self-join or window function to check activity on the next day for new users.
  • Revenue per DAU: sum revenue from purchases (deduplicated) divided by DAU, ensuring correct join keys and date alignment.
  • Handling of time zones and date boundaries: specify that dates are based on a consistent time zone (e.g., UTC) and define the 7-day window clearly.

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