Schema
Table
Setup
| Column Name | Type | Description |
|---|---|---|
| activity_id | integer | Watch activity identifier. |
| user_id | integer | User identifier. |
| show_id | integer | Show identifier. |
| device | varchar | Viewing device. |
Most
Watched
Show
Find the show with the highest number of views.
Table
Sample Data
Input
Output
Sample Input: watching_activity
| activity_id | user_id | show_id | device |
|---|---|---|---|
| 1 | 101 | 10 | TV |
| 2 | 102 | 20 | Mobile |
| 3 | 103 | 10 | TV |
| 4 | 104 | 10 | Laptop |
| 5 | 105 | 30 | TV |
| 6 | 106 | 20 | Mobile |
| 7 | 107 | 10 | TV |
| 8 | 108 | 20 | Tablet |
| 9 | 109 | 30 | TV |
Expected Output
| show_id | views |
|---|---|
| 10 | 4 |
SQL Editor
Run
Query
Waiting for query
| show_id | views |
|---|---|
| 10 | 4 |
Hints
Unlock
Clues
Hint 01: Each activity row is a view.
Hint 02: COUNT(*) by show_id.
Hint 03: Order descending and keep one row.
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 show_id, COUNT(*) AS views FROM watching_activity GROUP BY show_id ORDER BY views DESC LIMIT 1;
Explanation
Step By
Step
01
Group watch activity by show_id.
02
Count rows in each group as views.
03
Return the show with the highest count.
Related Questions