Write SQL window functions for streaks
Company: Flatiron Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Assume today is 2025-09-01. Using standard SQL (e.g., PostgreSQL), answer the following using window functions only (no procedural loops, no correlated subqueries). Schema and small samples:
Schema
- users(user_id INT, signup_date DATE)
- events(user_id INT, event_date DATE, event_name TEXT) -- one row per user per day per event
- orders(order_id INT, user_id INT, order_date DATE, amount DECIMAL(8,2))
Sample rows
users
user_id | signup_date
1 | 2025-08-20
2 | 2025-08-22
3 | 2025-08-25
events
user_id | event_date | event_name
1 | 2025-08-29 | login
1 | 2025-08-30 | login
1 | 2025-09-01 | login
2 | 2025-08-26 | login
2 | 2025-08-28 | login
2 | 2025-08-29 | login
2 | 2025-09-01 | login
3 | 2025-08-25 | login
3 | 2025-08-26 | login
3 | 2025-08-27 | login
3 | 2025-09-01 | login
orders
order_id | user_id | order_date | amount
10 | 1 | 2025-08-30 | 20.00
11 | 2 | 2025-08-27 | 15.00
12 | 2 | 2025-09-01 | 30.00
13 | 3 | 2025-08-26 | 12.00
Tasks
A) Return, for each user, the first 3-day consecutive activity streak whose streak-end falls within 2025-08-26 to 2025-09-01 inclusive. Activity is defined as having at least one events row (any event_name) on a date. Output: user_id, streak_start_date, streak_end_date. Use only window functions and date arithmetic (no self-joins on dates tables).
B) For each calendar day d in 2025-08-26..2025-09-01, compute: (i) DAU = distinct users with any event on d, (ii) revenue_d = sum of orders.amount with order_date = d, and (iii) rolling_7d_DAU_avg = 7-day trailing average of DAU ending on d. Also compute revenue_per_active_user = revenue_d / DAU with 4-decimal precision, treating division-by-zero as NULL. Return day, DAU, revenue_d, rolling_7d_DAU_avg, revenue_per_active_user.
C) Flag users who placed any order on date t and then had a strict 10-day inactivity gap (no events) immediately after t, followed by any event on a later date u. Return user_id, last_order_date = t, gap_days, first_post_gap_event_date = u. Only the earliest such gap per user.
Overview: This question evaluates proficiency with SQL window functions, date arithmetic, gap-and-island (streak) detection, rolling aggregates, and event-order sequencing to derive DAU, revenue metrics, and inactivity gaps.
Read the full Flatiron Health Data Scientist interview experience this question came from
First 3-Day Consecutive Activity Streak per User
Using the tables defined below, write a standard SQL query (using window functions and date arithmetic only; no procedural loops and no correlated subqueries) to return, for each user, the first 3-day consecutive activity streak whose streak_end falls between 2025-08-26 and 2025-09-01 inclusive. Activity is defined as having at least one row in the events table (any event_name) on a given date. A 3-day streak means the user is active on three consecutive calendar days. The output should include only users who have at least one such 3-day streak and must have the columns: user_id, streak_start_date, streak_end_date.
Tables
users(user_id INT, signup_date DATE)
events(user_id INT, event_date DATE, event_name VARCHAR(50))
orders(order_id INT, user_id INT, order_date DATE, amount DECIMAL(8,2))
Hints
- First collapse to one row per (user_id, event_date), then order each user’s dates and assign ROW_NUMBER().
- Use the trick of grouping by (event_date - row_number * 1 day) to find runs of consecutive dates, filter runs with length >= 3, then pick the earliest qualifying streak per user.
Daily Metrics and 7-Day Rolling DAU Average
Using the same tables, write a standard SQL query (using window functions and date arithmetic; no procedural loops and no correlated subqueries) to compute, for each calendar day d from 2025-08-26 through 2025-09-01 inclusive: (i) DAU = the number of distinct users with any event on d; (ii) revenue_d = the sum of orders.amount where order_date = d; (iii) rolling_7d_DAU_avg = the 7-day trailing average of DAU ending on d, computed over the sequence of calendar days (for the first few days, average over all available preceding days in the range); and (iv) revenue_per_active_user = revenue_d / DAU with 4-decimal precision, treating division-by-zero as NULL. The output columns should be: day, DAU, revenue_d, rolling_7d_DAU_avg, revenue_per_active_user, and must include all days in the date range even if DAU or revenue_d are zero.
Tables
users(user_id INT, signup_date DATE)
events(user_id INT, event_date DATE, event_name VARCHAR(50))
orders(order_id INT, user_id INT, order_date DATE, amount DECIMAL(8,2))
Hints
- Construct a calendar of dates for 2025-08-26 through 2025-09-01 and LEFT JOIN to events and orders to compute DAU and revenue per day.
- Use a window AVG over ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for the rolling 7-day DAU average, and use NULLIF in the denominator to avoid division-by-zero when computing revenue_per_active_user.
Detect Users with 10-Day Inactivity Gaps After Orders
Using the same tables, write a standard SQL query (using window functions and date arithmetic; no procedural loops and no correlated subqueries) to flag users who placed an order on some date t and then had a strict 10-day inactivity gap immediately after t (no events on any of the days t+1, t+2, ..., t+10), followed by at least one later event on a date u > t+10. For each such user, return only the earliest qualifying gap. The output columns should be: user_id, last_order_date = t, gap_days, first_post_gap_event_date = u, where gap_days is the number of full days between t and u, excluding both endpoints (equivalent to DATEDIFF(u, t) - 1 in days).
Tables
users(user_id INT, signup_date DATE)
events(user_id INT, event_date DATE, event_name VARCHAR(50))
orders(order_id INT, user_id INT, order_date DATE, amount DECIMAL(8,2))
Hints
- Join orders to events on the same user with different date conditions to identify events within the first 10 days after an order and those strictly after that window.
- Use ROW_NUMBER() to pick the first event after the 10-day gap for each order, filter out orders that have any events in days t+1..t+10, then apply another ROW_NUMBER() partitioned by user to keep only the earliest qualifying gap per user.