Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| user_id | integer | User identifier. |
| watch_date | date | Date watched. |
Daily
Active
Users
Find the number of active users per day.
Table
Sample Data
Input
Output
Sample Input: watching_activity
| user_id | watch_date |
|---|---|
| 101 | 2024-01-01 |
| 102 | 2024-01-01 |
| 101 | 2024-01-02 |
| 103 | 2024-01-02 |
| 104 | 2024-01-03 |
| 105 | 2024-01-03 |
| 106 | 2024-01-03 |
Expected Output
| watch_date | active_users |
|---|---|
| 2024-01-01 | 2 |
| 2024-01-02 | 2 |
| 2024-01-03 | 3 |
SQL Editor
Run
Query
Waiting for query
| watch_date | active_users |
|---|---|
| 2024-01-01 | 2 |
| 2024-01-02 | 2 |
| 2024-01-03 | 3 |
Hints
Unlock
Clues
Hint 01: Active means the user has a watch row that day.
Hint 02: COUNT(DISTINCT user_id) for each watch_date.
Hint 03: Order by date for readable output.
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.
SELECT watch_date, COUNT(DISTINCT user_id) AS active_users FROM watching_activity GROUP BY watch_date ORDER BY watch_date;
Explanation
Step By
Step
01
Group rows by watch_date.
02
Count unique users in each date group.
03
Return one row per day.
Related Questions