Flatiron Health·Data Scientist·Technical Phone Screen·Senior

Senior

Sep 2025Remote

Summary

SQL-heavy technical screen for a Data Scientist role at Flatiron Health. Three questions, all window-function-only constraints, progressively nastier. The kind of interview where you finish and genuinely aren't sure if you passed.

Questions Asked (3)

Given a table of user events (one row per user per day per event), identify each user's first 3-day consecutive activity streak whose end date falls within a specified 7-day window. Return user_id, streak_start_date, and streak_end_date using only window functions and date arithmetic.

Data ModelingAlgorithms & Data StructuresProduct Analytics & Metrics

Author's notes

This one took me a while to even parse.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, deduplicate events to one row per user per active day, then use the 'date minus row_number' trick to assign a group ID to consecutive active days. Within each group, compute the streak length and start/end dates, filter for streaks of at least 3 days, and finally select the first such streak per user whose end date falls within the specified 7-day window.

Pro tip: Always clarify whether the 7-day window is inclusive and whether 'first' means earliest start date or earliest end date; also confirm if the streak must be exactly 3 days or at least 3 days, as this changes the logic.

1. Deduplicate to daily activity

Use a subquery or CTE to select distinct user_id and event_date from the events table, ensuring one row per user per active day.

2. Identify consecutive day groups

For each user, assign a row number ordered by date and compute date minus row_number (or equivalent) to create a group identifier for consecutive active days.

3. Compute streak boundaries and length

Group by user_id and the group identifier, then calculate the minimum date as streak_start, maximum date as streak_end, and count as streak_length.

4. Filter and rank streaks

Filter for streak_length >= 3 and streak_end within the 7-day window, then use ROW_NUMBER() partitioned by user_id ordered by streak_start (or streak_end) to get the first streak per user.

5. Select final output

Return user_id, streak_start_date, and streak_end_date for the first qualifying streak per user.

Key Points to Mention

Deduplication of events to one row per user per day to avoid counting multiple events on the same day as separate streak days.
The 'date minus row_number' technique to group consecutive dates, which works because the difference remains constant for consecutive days.
Using window functions like ROW_NUMBER() and aggregation functions (MIN, MAX, COUNT) to compute streak boundaries and length.
Filtering for streaks of at least 3 days and ensuring the end date falls within the specified 7-day window (inclusive/exclusive as clarified).
Selecting the first streak per user using ROW_NUMBER() or RANK() with appropriate ordering (e.g., by streak_start_date).
Handling edge cases such as users with no qualifying streaks, multiple streaks ending in the window, and timezone considerations for date boundaries.

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

For each calendar day in a given date range, compute DAU, daily revenue, a 7-day trailing average of DAU, and revenue per active user (with division-by-zero handled as NULL). Use only window functions.

Product Analytics & MetricsData Modeling

Author's notes

Felt more comfortable here.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by aggregating raw event data to daily counts of active users and revenue, then use window functions to compute the 7-day trailing average of DAU and the revenue per active user with NULLIF to avoid division by zero. Ensure the final output includes all calendar days in the range, even those with zero activity, by generating a date spine and left joining the aggregates.

Pro tip: When computing trailing averages, specify ROWS BETWEEN 6 PRECEDING AND CURRENT ROW to get exactly 7 days, and use NULLIF(DAU, 0) to return NULL for revenue per active user when DAU is zero, as required.

1. Generate a complete date spine

Create a series of all calendar days in the given range to ensure no days are missing, even if there was no activity.

2. Aggregate daily metrics

From the raw event data, compute daily active users (distinct user count) and daily revenue (sum of revenue) for each day.

3. Join aggregates to date spine

Left join the daily aggregates to the date spine, replacing NULLs with 0 for DAU and revenue to handle days with no activity.

4. Compute trailing average and ratio using window functions

Use a window function to calculate the 7-day trailing average of DAU, and compute revenue per active user with NULLIF to handle division by zero.

Key Points to Mention

Use of a date spine (e.g., GENERATE_SERIES or a calendar table) to include all days in the range.
Definition of DAU as COUNT(DISTINCT user_id) per day.
Window function for trailing average: AVG(DAU) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
Handling division by zero with NULLIF(DAU, 0) to return NULL when DAU is zero.
Ensuring that days with zero activity are included and correctly handled (e.g., COALESCE to 0 for DAU and revenue).
Use of only window functions for the required calculations, avoiding self-joins or subqueries for the trailing average.

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

Flag users who placed an order on some date t and then had a strict 10-day gap with no events, followed by any event on a later date. Return user_id, the order date, the gap length in days, and the first post-gap event date. Only the earliest qualifying gap per user.

Data ModelingAlgorithms & Data StructuresRoot Cause Analysis

Author's notes

Hardest of the three.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a window function to find the next event date after each order, then filter for gaps of exactly 10 days. For each user, select the earliest qualifying gap by ordering by order date and using ROW_NUMBER.

Pro tip: Clarify whether 'strict 10-day gap' means exactly 10 days or at least 10 days, and whether the post-gap event can be any event type. Also, consider edge cases like multiple orders on the same day.

1. Identify orders and subsequent events

Extract all orders and all events per user, ensuring proper ordering by date. Use a window function like LEAD to find the next event date after each order.

2. Compute gap length

Calculate the difference in days between the order date and the next event date. Filter for gaps equal to 10 days (or as defined).

3. Select earliest qualifying gap per user

For each user, rank the qualifying gaps by order date and pick the earliest using ROW_NUMBER or a subquery with MIN.

4. Return required columns

Output user_id, order date, gap length, and first post-gap event date. Ensure the result is deduplicated per user.

Key Points to Mention

Use of window functions (LEAD/LAG) to find next event after order
Date arithmetic to compute gap in days
Filtering for exactly 10-day gap (strict interpretation)
Handling multiple orders per user and selecting earliest
Ensuring the post-gap event is any event type
Performance considerations for large datasets (indexing, partitioning)

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