Task
Meta’s Instagram Reels team wants to identify the strongest creators by total watch time over the last 7 days. Write a SQL query to rank creators within each country and return the top 5 creators per country.
Requirements
- Use only watch events from the last 7 days based on
watch_date.
- Sum
watch_time_seconds for each creator within each country.
- Rank creators separately inside each country by total watch time, highest first.
- Return only the top 5 creators per country.
- Include the creator name, country, total watch time, and rank.
- If two creators tie on total watch time, break ties by smaller
creator_id.
Table Definitions
reels_watch_events
| column | type | description |
|---|
| event_id | INT | Unique watch event id |
| viewer_id | INT | Viewer who watched the Reel |
| creator_id | INT | Creator whose Reel was watched |
| watch_date | DATE | Date of the watch event |
| watch_time_seconds | INT | Watch time for the event |
| surface | VARCHAR(50) | Meta surface where the watch happened |
| | |
reels_creators
| column | type | description |
|---|
| creator_id | INT | Unique creator id |
| creator_name | VARCHAR(100) | Creator display name |
| country | VARCHAR(50) | Creator’s home country |
| account_status | VARCHAR(20) | Current creator account status |
Sample Data
reels_creators
| creator_id | creator_name | country | account_status |
|---|
| 104 | Priya Shah | India | active |
| 101 | Maya Chen | US | active |
| 109 | Sofia Rossi | Italy | active |
| 103 | Lucas Martin | Brazil | active |
| 106 | Ana Gomez | Brazil | active |
| 102 | Jordan Lee | US | active |
| 108 | Emma Wilson | UK | active |
| 105 | Ravi Kumar | India | paused |
| 107 | Noah Brown | US | active |
| 110 | Liam Carter | Canada | active |
reels_watch_events
| event_id | viewer_id | creator_id | watch_date | watch_time_seconds | surface |
|---|
| 11 | 9001 | 101 | 2025-05-14 | 300 | ig_reels |
| 12 | 9002 | 101 | 2025-05-12 | 200 | ig_reels |
| 13 | 9003 | 102 | 2025-05-13 | 500 | ig_reels |
| 14 | 9004 | 102 | 2025-05-08 | 100 | ig_reels |
| 15 | 9005 | 107 | 2025-05-10 | 450 | ig_reels |
| 16 | 9006 | 103 | 2025-05-11 | 250 | ig_reels |
| 17 | 9007 | 106 | 2025-05-14 | 350 | ig_reels |
| 18 | 9008 | 104 | 2025-05-09 | 600 | ig_reels |
| 19 | 9009 | 105 | 2025-05-07 | 700 | ig_reels |
| 20 | 9010 | 108 | 2025-05-13 | 0 | ig_reels |
| 21 | 9011 | 110 | 2025-05-14 | 150 | fb_groups |
| 22 | 9012 | 999 | 2025-05-14 | 400 | ig_reels |
Expected Output
| country | creator_id | creator_name | total_watch_time_seconds | country_rank |
|---|
| Brazil | 106 | Ana Gomez | 350 | 1 |
| Brazil | 103 | Lucas Martin | 250 | 2 |
| Canada | 110 | Liam Carter | 150 | 1 |
| India | 104 | Priya Shah | 600 | 1 |
| Italy | 109 | Sofia Rossi | 0 | 1 |
| UK | 108 | Emma Wilson | 0 | 1 |
| US | 102 | Jordan Lee | 600 | 1 |
| US | 101 | Maya Chen | 500 | 2 |
| US | 107 | Noah Brown | 450 | 3 |
