Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| user_id | integer | User identifier. |
| content_id | integer | Watched content identifier. |
| watch_date | date | Date watched. |
First
Content
Watched
Find the first content watched by each user.
Table
Sample Data
Input
Output
Sample Input: watching_activity
| user_id | content_id | watch_date |
|---|---|---|
| 101 | 1 | 2024-01-01 |
| 101 | 2 | 2024-01-03 |
| 102 | 3 | 2024-01-02 |
| 102 | 4 | 2024-01-05 |
| 103 | 2 | 2024-01-04 |
| 103 | 1 | 2024-01-01 |
| 104 | 3 | 2024-01-06 |
Expected Output
| user_id | content_id |
|---|---|
| 101 | 1 |
| 102 | 3 |
| 103 | 1 |
| 104 | 3 |
SQL Editor
Run
Query
Waiting for query
| user_id | content_id |
|---|---|
| 101 | 1 |
| 102 | 3 |
| 103 | 1 |
| 104 | 3 |
Hints
Unlock
Clues
Hint 01: Window functions are perfect for first-row-per-group problems.
Hint 02: Use ROW_NUMBER() partitioned by user_id.
Hint 03: Keep rn = 1.
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 ranked_activity AS (
SELECT
user_id,
content_id,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY watch_date
) AS rn
FROM watching_activity
)
SELECT
user_id,
content_id
FROM ranked_activity
WHERE rn = 1;
Explanation
Step By
Step
01
Rank every watch row inside its user_id group by watch_date.
02
Assign the earliest row rn = 1.
03
Select user_id and content_id from those first rows.
Related Questions