Table Schema
Inspect
Table
| Column | Type |
|---|---|
| user_id | integer |
| open_date | date |
App
Engagement
Find users who opened the app on 3 consecutive days.
Inspect
Sample Data
Input
Output
Sample Input: app_usage
| user_id | open_date |
|---|---|
| 101 | 2024-01-01 |
| 101 | 2024-01-02 |
| 101 | 2024-01-03 |
| 102 | 2024-01-01 |
| 102 | 2024-01-03 |
| 103 | 2024-01-05 |
| 104 | 2024-01-06 |
Expected Output
| user_id |
|---|
| 101 |
SQL Editor
Run
Query
Waiting for query
| user_id |
|---|
| 101 |
Hints
Unlock
Clues
Hint 01: Remove duplicate open dates first.
Hint 02: Use date minus row number to form streak groups.
Hint 03: A 3 day streak has COUNT(*) >= 3.
Solution
Locked
Answer
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
WITH distinct_days AS (
SELECT DISTINCT user_id, open_date
FROM app_usage
),
streaks AS (
SELECT
user_id,
open_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY open_date)::int AS streak_group
FROM distinct_days
)
SELECT user_id
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3;Explanation
Step By
Step
01
Deduplicate user-date pairs.
02
Build consecutive date groups using ROW_NUMBER.
03
Keep users with a group of at least 3 days.
Related Questions