Rank Reels Creators by Country

Medium

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

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

Schema

reels_creators

Column	Type	Description
creator_idPK	INT	Unique Instagram Reels creator identifier
creator_name	VARCHAR(100)	Creator display name
country	VARCHAR(50)	Creator home country
account_status	VARCHAR(20)	Creator account status such as active or paused

reels_watch_events

Column	Type	Description
event_idPK	INT	Unique watch event identifier
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 contributed by the event in seconds
surface	VARCHAR(50)	Meta surface where the watch happened

Sample Data

Example 1

Input

{"reels_creators":[["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"],["111","Ana Gomez","Kenya","restricted"],["112","Arjun Mehta","Philippines","under_review"],["113","Mila No

Output

[["Brazil","103","Lucas Martin","0","1"],["Brazil","106","Ana Gomez","0","2"],["Canada","110","Liam Carter","0","1"],["Egypt","129","Ana Gomez","0","1"],["France","128","Diego Alvarez","0","1"],["Germany","118","Emma Wilson","0","1"],["Germany","127","Zoe Turner","0","2"],["India","104","Priya Shah","0","1"],["India","105","Ravi Kumar","0","2"],["India","124","Ava Patel","0","3"],["Indonesia","116","Sara Ahmed","0","1"],["Italy","109","Sofia Rossi","0","1"],["Italy","117","Jordan Lee","0","2"],[

Problem

Task

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

Schema

reels_creators

Column	Type	Description
creator_idPK	INT	Unique Instagram Reels creator identifier
creator_name	VARCHAR(100)	Creator display name
country	VARCHAR(50)	Creator home country
account_status	VARCHAR(20)	Creator account status such as active or paused

reels_watch_events

Column	Type	Description
event_idPK	INT	Unique watch event identifier
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 contributed by the event in seconds
surface	VARCHAR(50)	Meta surface where the watch happened

Sample Data

Example 1

Input

{"reels_creators":[["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"],["111","Ana Gomez","Kenya","restricted"],["112","Arjun Mehta","Philippines","under_review"],["113","Mila No

Output

[["Brazil","103","Lucas Martin","0","1"],["Brazil","106","Ana Gomez","0","2"],["Canada","110","Liam Carter","0","1"],["Egypt","129","Ana Gomez","0","1"],["France","128","Diego Alvarez","0","1"],["Germany","118","Emma Wilson","0","1"],["Germany","127","Zoe Turner","0","2"],["India","104","Priya Shah","0","1"],["India","105","Ravi Kumar","0","2"],["India","124","Ava Patel","0","3"],["Indonesia","116","Sara Ahmed","0","1"],["Italy","109","Sofia Rossi","0","1"],["Italy","117","Jordan Lee","0","2"],[

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Reels Cohort 7-Day Rolling RetentionEasy

Top Countries by DAU GrowthMedium

IG Reels Posting SpanHard

Next question

Task

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