Coding questions · TikTok
Write SQL for TikTok Live creator metrics
This question evaluates SQL data-manipulation competencies such as joins, aggregations, group-by and window functions, handling missing activity rows, date-range filtering, country partitioning, and deterministic tie-breaking for top-k metrics.
You are analyzing TikTok Live sessions and their engagement.
Tables
live_room
Each row is a Live session (“room”) launched by a creator.
| column | type | description |
|---|---|---|
date | DATE | Calendar date of the Live session (assume UTC). |
creator_id | BIGINT | Creator who launched the Live. |
room_id | BIGINT | Unique id of the Live room. |
country | STRING | Country where the Live room is attributed (e.g., US, ID, UK). |
live_type | STRING | Device type used to go live (e.g., mobile, pc). |
Assume PK: (date, room_id).
live_room_activity
Each row contains aggregated engagement for a room on that date.
| column | type | description |
|---|---|---|
date | DATE | Same date as the Live session. |
room_id | BIGINT | Live room id. |
watch_cnt | INT | Number of viewers who watched that room. |
gift_recieved_cnt | INT | Number of gifts received in that room. |
Assume PK: (date, room_id).
Join relationship
Join the tables on (date, room_id). If a room has no activity row, assume watch_cnt = 0 and gift_recieved_cnt = 0.
Questions (write SQL only)
Q1
For the period 2022-10-01 to 2022-10-07 (inclusive), in the US, find how many creators launched more than 3 mobile Live sessions.
Output:
creator_count(number of distinctcreator_idmeeting the condition)
Q2 (Medium)
In October 2022, find the top 5 creators by total gifts received for each country.
Notes:
- Total gifts received for a creator in a country = sum of
gift_recieved_cntacross all that creator’s rooms in that country during October 2022. - If ties occur at rank 5, break ties by
creator_idascending (or specify a deterministic tie-break).
Output (recommended):
countrycreator_idtotal_gifts_receivedrank_in_country(1–5)
Q3 (Hard)
For yesterday’s date (relative to query run date), calculate the count of creators who satisfy both:
- The creator went live more than once that day (i.e., launched at least 2 rooms).
- Every Live room they launched that day had
watch_cnt >= 500.
Output:
qualified_creator_count
US creators with more than 3 mobile Live sessions (2022-10-01 to 2022-10-07)
TikTok captures Live room launches in live_room.
For the time period from 2022-10-01 to 2022-10-07 (inclusive), in the US, find how many distinct creators launched more than 3 mobile Live sessions.
Return a single row with the count of such creators.
Table live_room
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| creator_id | BIGINT | NOT NULL |
| room_id | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| live_type | VARCHAR(10) | NOT NULL |
Sample data:
| country | creator_id | date | live_type | room_id |
|---|---|---|---|---|
| US | 2315923 | 2022-10-01 | mobile | 3268 |
| US | 2315923 | 2022-10-01 | pc | 3269 |
| US | 2315923 | 2022-10-02 | mobile | 3274 |
| US | 2315923 | 2022-10-03 | mobile | 3275 |
| US | 2315923 | 2022-10-05 | mobile | 3276 |
| US | 2328567 | 2022-10-03 | pc | 3271 |
| US | 2328567 | 2022-10-03 | pc | 3272 |
| US | 2328567 | 2022-10-04 | mobile | 3277 |
| US | 2328567 | 2022-10-06 | mobile | 3278 |
| US | 2328567 | 2022-10-07 | mobile | 3279 |
| US | 2400000 | 2022-10-02 | mobile | 3283 |
| US | 2411111 | 2022-10-12 | pc | 3285 |
| ID | 2394226 | 2022-10-02 | mobile | 3270 |
| UK | 2378123 | 2022-10-04 | mobile | 3273 |
| UK | 2378123 | 2022-10-10 | pc | 3280 |
| ID | 2394226 | 2022-10-12 | mobile | 3281 |
| UK | 2380000 | 2022-10-15 | mobile | 3282 |
| US | 2400000 | 2022-10-20 | mobile | 3284 |
| ID | 2399999 | 2022-10-20 | mobile | 3286 |
| US | 3001 | 2025-05-31 | mobile | 9001 |
| US | 3001 | 2025-05-31 | pc | 9002 |
| UK | 3002 | 2025-05-31 | mobile | 9003 |
| UK | 3002 | 2025-05-31 | mobile | 9004 |
| US | 3003 | 2025-05-31 | mobile | 9005 |
Table live_room_activity
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| room_id | INT | NOT NULL |
| watch_cnt | INT | NOT NULL |
| gift_recieved_cnt | INT | NOT NULL |
Sample data:
| date | gift_recieved_cnt | room_id | watch_cnt |
|---|---|---|---|
| 2022-10-01 | 5000 | 3268 | 2000 |
| 2022-10-01 | 1000 | 3269 | 500 |
| 2022-10-02 | 1500 | 3274 | 1200 |
| 2022-10-03 | 200 | 3275 | 700 |
| 2022-10-05 | 800 | 3276 | 900 |
| 2022-10-02 | 2000 | 3270 | 2500 |
| 2022-10-03 | 500 | 3271 | 900 |
| 2022-10-03 | 70 | 3272 | 300 |
| 2022-10-04 | 1000 | 3273 | 1000 |
| 2022-10-04 | 1200 | 3277 | 650 |
| 2022-10-06 | 900 | 3278 | 800 |
| 2022-10-07 | 50 | 3279 | 550 |
| 2022-10-10 | 3000 | 3280 | 1400 |
| 2022-10-12 | 500 | 3281 | 1100 |
| 2022-10-15 | 4000 | 3282 | 2000 |
| 2022-10-02 | 6000 | 3283 | 1800 |
| 2022-10-20 | 100 | 3284 | 400 |
| 2022-10-12 | 700 | 3285 | 900 |
| 2022-10-20 | 2500 | 3286 | 1500 |
| 2025-05-31 | 100 | 9001 | 600 |
| 2025-05-31 | 50 | 9002 | 500 |
| 2025-05-31 | 10 | 9003 | 800 |
| 2025-05-31 | 20 | 9004 | 400 |
| 2025-05-31 | 5 | 9005 | 1000 |
Expected output
One row with the number of US creators who launched more than 3 mobile Live sessions between 2022-10-01 and 2022-10-07 (inclusive).
| creator_cnt |
|---|
| 1 |
Hints
- Filter to the date range, country='US', and live_type='mobile'.
- Group by creator_id and keep creators with COUNT(*) > 3, then count them.
Top 5 creators by gifts received per country (October 2022)
TikTok captures Live room launches in live_room and per-room metrics in live_room_activity.
In October 2022 (2022-10-01 to 2022-10-31 inclusive), find the top 5 creators by total gifts received for each country.
Output columns:
- country
- creator_id
- total_gift_received
- country_rank (1 = highest total gifts for that country)
If a country has fewer than 5 creators, return all of them. Break ties by smaller creator_id first.
Table live_room
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| creator_id | BIGINT | NOT NULL |
| room_id | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| live_type | VARCHAR(10) | NOT NULL |
Sample data:
| country | creator_id | date | live_type | room_id |
|---|---|---|---|---|
| US | 2315923 | 2022-10-01 | mobile | 3268 |
| US | 2315923 | 2022-10-01 | pc | 3269 |
| US | 2315923 | 2022-10-02 | mobile | 3274 |
| US | 2315923 | 2022-10-03 | mobile | 3275 |
| US | 2315923 | 2022-10-05 | mobile | 3276 |
| US | 2328567 | 2022-10-03 | pc | 3271 |
| US | 2328567 | 2022-10-03 | pc | 3272 |
| US | 2328567 | 2022-10-04 | mobile | 3277 |
| US | 2328567 | 2022-10-06 | mobile | 3278 |
| US | 2328567 | 2022-10-07 | mobile | 3279 |
| US | 2400000 | 2022-10-02 | mobile | 3283 |
| US | 2400000 | 2022-10-20 | mobile | 3284 |
| US | 2411111 | 2022-10-12 | pc | 3285 |
| ID | 2394226 | 2022-10-02 | mobile | 3270 |
| ID | 2394226 | 2022-10-12 | mobile | 3281 |
| ID | 2399999 | 2022-10-20 | mobile | 3286 |
| UK | 2378123 | 2022-10-04 | mobile | 3273 |
| UK | 2378123 | 2022-10-10 | pc | 3280 |
| UK | 2380000 | 2022-10-15 | mobile | 3282 |
| US | 3001 | 2025-05-31 | mobile | 9001 |
| US | 3001 | 2025-05-31 | pc | 9002 |
| UK | 3002 | 2025-05-31 | mobile | 9003 |
| UK | 3002 | 2025-05-31 | mobile | 9004 |
| US | 3003 | 2025-05-31 | mobile | 9005 |
Table live_room_activity
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| room_id | INT | NOT NULL |
| watch_cnt | INT | NOT NULL |
| gift_recieved_cnt | INT | NOT NULL |
Sample data:
| date | gift_recieved_cnt | room_id | watch_cnt |
|---|---|---|---|
| 2022-10-01 | 5000 | 3268 | 2000 |
| 2022-10-01 | 1000 | 3269 | 500 |
| 2022-10-02 | 1500 | 3274 | 1200 |
| 2022-10-03 | 200 | 3275 | 700 |
| 2022-10-05 | 800 | 3276 | 900 |
| 2022-10-02 | 2000 | 3270 | 2500 |
| 2022-10-03 | 500 | 3271 | 900 |
| 2022-10-03 | 70 | 3272 | 300 |
| 2022-10-04 | 1000 | 3273 | 1000 |
| 2022-10-04 | 1200 | 3277 | 650 |
| 2022-10-06 | 900 | 3278 | 800 |
| 2022-10-07 | 50 | 3279 | 550 |
| 2022-10-10 | 3000 | 3280 | 1400 |
| 2022-10-12 | 500 | 3281 | 1100 |
| 2022-10-15 | 4000 | 3282 | 2000 |
| 2022-10-02 | 6000 | 3283 | 1800 |
| 2022-10-20 | 100 | 3284 | 400 |
| 2022-10-12 | 700 | 3285 | 900 |
| 2022-10-20 | 2500 | 3286 | 1500 |
| 2025-05-31 | 100 | 9001 | 600 |
| 2025-05-31 | 50 | 9002 | 500 |
| 2025-05-31 | 10 | 9003 | 800 |
| 2025-05-31 | 20 | 9004 | 400 |
| 2025-05-31 | 5 | 9005 | 1000 |
Expected output
Up to 5 rows per country for October 2022, ordered within each country by total_gift_received desc, then creator_id asc. Columns: country, creator_id, total_gift_received, country_rank.
| country | country_rank | creator_id | total_gift_received |
|---|---|---|---|
| ID | 1 | 2394226 | 2500 |
| ID | 2 | 2399999 | 2500 |
| UK | 1 | 2378123 | 4000 |
| UK | 2 | 2380000 | 4000 |
| US | 1 | 2315923 | 8500 |
| US | 2 | 2400000 | 6100 |
| US | 3 | 2328567 | 2720 |
| US | 4 | 2411111 | 700 |
Hints
- Join
live_roomtolive_room_activityon (date, room_id) to attribute gifts to creators and countries. - Aggregate gifts per (country, creator_id), then use a window function to rank within each country.
Creators who went live more than once with watch_cnt >= 500 in every room (2025-05-31)
Treat “yesterday” as 2025-05-31.
For 2025-05-31, calculate the number of creators who satisfy BOTH criteria:
- The creator went live more than once on that date.
- Every Live room they launched that date has watch_cnt >= 500.
Return a single row with the count of such creators.
Table live_room
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| creator_id | BIGINT | NOT NULL |
| room_id | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| live_type | VARCHAR(10) | NOT NULL |
Sample data:
| country | creator_id | date | live_type | room_id |
|---|---|---|---|---|
| US | 2315923 | 2022-10-01 | mobile | 3268 |
| US | 2315923 | 2022-10-01 | pc | 3269 |
| ID | 2394226 | 2022-10-02 | mobile | 3270 |
| US | 2328567 | 2022-10-03 | pc | 3271 |
| US | 2328567 | 2022-10-03 | pc | 3272 |
| UK | 2378123 | 2022-10-04 | mobile | 3273 |
| US | 3001 | 2025-05-31 | mobile | 9001 |
| US | 3001 | 2025-05-31 | pc | 9002 |
| UK | 3002 | 2025-05-31 | mobile | 9003 |
| UK | 3002 | 2025-05-31 | mobile | 9004 |
| US | 3003 | 2025-05-31 | mobile | 9005 |
Table live_room_activity
| column | type | constraints |
|---|---|---|
| date | DATE | NOT NULL |
| room_id | INT | NOT NULL |
| watch_cnt | INT | NOT NULL |
| gift_recieved_cnt | INT | NOT NULL |
Sample data:
| date | gift_recieved_cnt | room_id | watch_cnt |
|---|---|---|---|
| 2022-10-01 | 5000 | 3268 | 2000 |
| 2022-10-01 | 1000 | 3269 | 500 |
| 2022-10-02 | 2000 | 3270 | 2500 |
| 2022-10-03 | 500 | 3271 | 900 |
| 2022-10-03 | 70 | 3272 | 300 |
| 2022-10-04 | 1000 | 3273 | 1000 |
| 2025-05-31 | 100 | 9001 | 600 |
| 2025-05-31 | 50 | 9002 | 500 |
| 2025-05-31 | 10 | 9003 | 800 |
| 2025-05-31 | 20 | 9004 | 400 |
| 2025-05-31 | 5 | 9005 | 1000 |
Expected output
One row with the number of creators who went live more than once on 2025-05-31 and have watch_cnt >= 500 for every room they launched that day.
| creators_cnt |
|---|
| 1 |
Hints
- First restrict to 2025-05-31 and join activity to get watch_cnt per room.
- Use HAVING COUNT(*) > 1 and enforce the 'every room' condition via MIN(watch_cnt) >= 500 (or SUM(CASE WHEN watch_cnt < 500 THEN 1 END)=0).