Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| user_id | integer | User identifier. |
| watch_date | date | Date watched. |
Binge
Watchers
Find users who watched content on 3 consecutive days.
Table
Sample Data
Input
Output
Sample Input: watching_activity
| user_id | watch_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: Deduplicate user/date rows first.
Hint 02: Self-join the table to the next day and the day after.
Hint 03: Return DISTINCT user_id.
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 daily_activity AS (
SELECT DISTINCT
user_id,
watch_date
FROM watching_activity
)
SELECT DISTINCT d1.user_id
FROM daily_activity d1
JOIN daily_activity d2
ON d1.user_id = d2.user_id
AND d2.watch_date = d1.watch_date + INTERVAL '1 day'
JOIN daily_activity d3
ON d1.user_id = d3.user_id
AND d3.watch_date = d1.watch_date + INTERVAL '2 days';
Explanation
Step By
Step
01
Create one row per user per watch date.
02
Match each date to the same user's next two dates.
03
Select users where all three dates exist.
Related Questions