Join Our 5-Week ML/AI Engineer Interview Bootcamp 🚀 led by ML Tech Leads at FAANGs
14. Top Viewers
TikTokWhich users have the highest number of clips watched, and what are their user_name and total_views? Compute total_views as the count of view events in tiktok_fct_views per viewer_id, and return all users tied for the maximum total_views. Order the results by user_name in ascending order.
| Column Name | Type |
|---|---|
| user_id | int64 |
| user_name | object |
| signup_ts | object |
| country | object |
| suspicious_account | int64 |
| Column Name | Type |
|---|---|
| viewer_id | int64 |
| creator_id | int64 |
| view_ts | object |
| device_type | object |
| viewed_to_completion | int64 |
| sound_id | int64 |
| has_promotion | int64 |
| Column Name | Type |
|---|---|
| user_name | object |
| total_views | int64 |
Constraints
Group by viewer_id; count view-event rows.
Return all users tied for maximum count.
Output user_name, total_views; sort by user_name.
Hint 1
Start by aggregating tiktok_fct_views by viewer_id and counting rows to get total_views.
Hint 2
Compute the maximum total_views, then filter the aggregated results to only those equal to that max (this keeps ties).
Hint 3
Join the filtered viewers to tiktok_dim_users to fetch user_name, then select (user_name, total_views) and sort by user_name ascending.
Roles
Companies
TikTokLevels
Tags
Python Editor