Identify the top 10 users with the highest total hours watched in the last 30 days.
Schema
| Column Name | Type | Description |
|---|---|---|
| activity_id | integer | Unique watch activity identifier. |
| user_id | integer | User who watched content. |
| watch_date | date | Date the user watched content. |
| hours_watched | decimal | Hours watched in the activity. |
Sample Data
| activity_id | user_id | watch_date | hours_watched |
|---|---|---|---|
| 1 | 101 | 2024-01-01 | 2.5 |
| 2 | 102 | 2024-01-02 | 1.0 |
| 3 | 101 | 2024-01-03 | 3.0 |
| 4 | 103 | 2024-01-04 | 4.0 |
| 5 | 102 | 2024-01-05 | 2.0 |
| 6 | 104 | 2024-01-06 | 5.0 |
| 7 | 101 | 2024-01-07 | 1.5 |
| 8 | 105 | 2024-01-08 | 2.2 |
| user_id | total_hours |
|---|---|
| 101 | 7.0 |
| 104 | 5.0 |
| 103 | 4.0 |
| 102 | 3.0 |
| 105 | 2.2 |
SQL Editor
| user_id | total_hours |
|---|---|
| 101 | 7.0 |
| 104 | 5.0 |
| 103 | 4.0 |
| 102 | 3.0 |
| 105 | 2.2 |
Hints
Solution
Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.
SELECT user_id, SUM(hours_watched) AS total_hours FROM watching_activity WHERE watch_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY user_id ORDER BY total_hours DESC LIMIT 10;
Explanation
Filter to the last 30 days of watch activity.
Aggregate hours_watched for each user_id.
Sort by total_hours descending and keep the first 10 rows.
Related Questions
