Netflix·Data Scientist·Technical Phone Screen·Senior

Senior

May 2026

Summary

Netflix data scientist interview with a SQL heavy question around consecutive ordering streaks. Pretty niche problem, felt like it was designed to filter out people who only know basic aggregations.

Questions Asked (1)

Given an orders table with user IDs and order dates, find the longest streak of consecutive calendar days on which each user placed at least one order.

Algorithms & Data StructuresData ModelingProduct Analytics & Metrics

Author's notes

I knew it was a gaps-and-islands type problem the second I saw it, which helped.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by deduplicating orders to one row per user per calendar day, then use a date-grouping trick (subtract a row number from the order date) to assign a constant group key to consecutive days. Finally, count the size of each group per user and take the maximum to get the longest streak.

Pro tip: Mention that this pattern (date minus row number) is a classic gaps-and-islands technique, and note that it works because consecutive dates increment by 1 while the row number also increments by 1, keeping the difference constant. Also, clarify how you'd handle time zones or partial days if the data has timestamps.

1. Deduplicate to daily activity

Collapse multiple orders per user per day into a single distinct (user_id, order_date) row, since the streak only cares about whether at least one order occurred on a calendar day.

2. Assign a group key for consecutive days

For each user, compute a group identifier as order_date minus a sequential row number (ordered by date). Consecutive dates will share the same group key.

3. Count streak lengths

Group by user_id and the group key, then count the number of days in each group. Each count represents the length of a consecutive-day streak.

4. Find the longest streak per user

For each user, take the maximum count from step 3 to get their longest streak. Optionally, also return the start and end dates of that streak.

Key Points to Mention

Deduplication: multiple orders on the same day should count as one day for the streak.
Gaps-and-islands technique: using date minus row_number() to identify consecutive sequences.
Window functions: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date).
Handling ties or multiple orders per day: use DISTINCT or GROUP BY before applying the window function.
Edge cases: users with only one order, non-consecutive days, and time zone considerations if timestamps are involved.
Performance: the approach is O(n log n) due to sorting, and can be optimized with indexes on (user_id, order_date).

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