Table Schema
Inspect
Table
| Column | Type |
|---|---|
| upload_id | integer |
| user_id | integer |
| upload_date | date |
Photo
Upload
Activity
Find users who uploaded photos on 3 consecutive days.
Inspect
Sample Data
Input
Output
Sample Input: photo_uploads
| upload_id | user_id | upload_date |
|---|---|---|
| 1 | 101 | 2024-01-01 |
| 2 | 101 | 2024-01-02 |
| 3 | 101 | 2024-01-03 |
| 4 | 102 | 2024-01-01 |
| 5 | 102 | 2024-01-03 |
| 6 | 103 | 2024-01-05 |
| 7 | 104 | 2024-01-06 |
| 8 | 104 | 2024-01-07 |
Expected Output
| user_id |
|---|
| 101 |
SQL Editor
Run
Query
Waiting for query
| user_id |
|---|
| 101 |
Hints
Unlock
Clues
Hint 01: Work with distinct upload dates per user.
Hint 02: Create streak groups with ROW_NUMBER.
Hint 03: Require a group length of at least 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, upload_date
FROM photo_uploads
),
streaks AS (
SELECT
user_id,
upload_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY upload_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 upload dates.
02
Normalize consecutive dates into the same group.
03
Return users with a 3 day upload streak.
Related Questions