Coding questions · Flatiron Health
Write SQL window functions for streaks
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.
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.
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-08-20 | 1 |
| 2025-08-22 | 2 |
| 2025-08-25 | 3 |
| 2025-08-18 | 4 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
Sample data:
| event_date | event_name | user_id |
|---|---|---|
| 2025-08-29 | login | 1 |
| 2025-08-30 | login | 1 |
| 2025-09-01 | login | 1 |
| 2025-08-26 | login | 2 |
| 2025-08-28 | login | 2 |
| 2025-08-29 | login | 2 |
| 2025-09-01 | login | 2 |
| 2025-08-25 | login | 3 |
| 2025-08-26 | login | 3 |
| 2025-08-27 | login | 3 |
| 2025-09-01 | login | 3 |
| 2025-08-20 | login | 4 |
| 2025-09-10 | login | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(8,2) | NOT NULL |
Sample data:
| amount | order_date | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-30 | 10 | 1 |
| 15 | 2025-08-27 | 11 | 2 |
| 30 | 2025-09-01 | 12 | 2 |
| 12 | 2025-08-26 | 13 | 3 |
| 50 | 2025-08-20 | 14 | 4 |
Expected output
One row per user who has at least one 3-day streak of consecutive daily activity, where the streak_end_date is between 2025-08-26 and 2025-09-01 inclusive. For the sample data, only user 3 has such a streak (2025-08-25, 2025-08-26, 2025-08-27).
| streak_end_date | streak_start_date | user_id |
|---|---|---|
| 2025-08-27 | 2025-08-25 | 3 |
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.
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-08-20 | 1 |
| 2025-08-22 | 2 |
| 2025-08-25 | 3 |
| 2025-08-18 | 4 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
Sample data:
| event_date | event_name | user_id |
|---|---|---|
| 2025-08-29 | login | 1 |
| 2025-08-30 | login | 1 |
| 2025-09-01 | login | 1 |
| 2025-08-26 | login | 2 |
| 2025-08-28 | login | 2 |
| 2025-08-29 | login | 2 |
| 2025-09-01 | login | 2 |
| 2025-08-25 | login | 3 |
| 2025-08-26 | login | 3 |
| 2025-08-27 | login | 3 |
| 2025-09-01 | login | 3 |
| 2025-08-20 | login | 4 |
| 2025-09-10 | login | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(8,2) | NOT NULL |
Sample data:
| amount | order_date | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-30 | 10 | 1 |
| 15 | 2025-08-27 | 11 | 2 |
| 30 | 2025-09-01 | 12 | 2 |
| 12 | 2025-08-26 | 13 | 3 |
| 50 | 2025-08-20 | 14 | 4 |
Expected output
One row per calendar day between 2025-08-26 and 2025-09-01 inclusive, showing DAU, total revenue for that day, the trailing 7-day average of DAU over the date sequence, and revenue_per_active_user as revenue_d / DAU (NULL when DAU = 0).
| DAU | day | revenue_d | revenue_per_active_user | rolling_7d_DAU_avg |
|---|---|---|---|---|
| 2 | 2025-08-26 | 12 | 6 | 2 |
| 1 | 2025-08-27 | 15 | 15 | 1.5 |
| 1 | 2025-08-28 | 0 | 0 | 1.3333 |
| 2 | 2025-08-29 | 0 | 0 | 1.5 |
| 1 | 2025-08-30 | 20 | 20 | 1.4 |
| 0 | 2025-08-31 | 0 | NULL | 1.1667 |
| 3 | 2025-09-01 | 30 | 10 | 1.4286 |
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).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|---|
| 2025-08-20 | 1 |
| 2025-08-22 | 2 |
| 2025-08-25 | 3 |
| 2025-08-18 | 4 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_name | VARCHAR(50) | NOT NULL |
Sample data:
| event_date | event_name | user_id |
|---|---|---|
| 2025-08-29 | login | 1 |
| 2025-08-30 | login | 1 |
| 2025-09-01 | login | 1 |
| 2025-08-26 | login | 2 |
| 2025-08-28 | login | 2 |
| 2025-08-29 | login | 2 |
| 2025-09-01 | login | 2 |
| 2025-08-25 | login | 3 |
| 2025-08-26 | login | 3 |
| 2025-08-27 | login | 3 |
| 2025-09-01 | login | 3 |
| 2025-08-20 | login | 4 |
| 2025-09-10 | login | 4 |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
| amount | DECIMAL(8,2) | NOT NULL |
Sample data:
| amount | order_date | order_id | user_id |
|---|---|---|---|
| 20 | 2025-08-30 | 10 | 1 |
| 15 | 2025-08-27 | 11 | 2 |
| 30 | 2025-09-01 | 12 | 2 |
| 12 | 2025-08-26 | 13 | 3 |
| 50 | 2025-08-20 | 14 | 4 |
Expected output
One row per user who has at least one order followed by at least 10 consecutive days with no events and then a later event, reporting only the earliest such gap per user. In the sample data, user 4 ordered on 2025-08-20, had no events from 2025-08-21 through 2025-08-30 (a 10-day inactivity window is satisfied and actually extends longer), and then had an event on 2025-09-10, yielding 20 gap days between order and next event.
| first_post_gap_event_date | gap_days | last_order_date | user_id |
|---|---|---|---|
| 2025-09-10 | 20 | 2025-08-20 | 4 |
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.