You are analyzing TikTok Live sessions and their engagement.
live_roomEach 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_activityEach 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 the tables on (date, room_id). If a room has no activity row, assume watch_cnt = 0 and gift_recieved_cnt = 0.
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 distinct
creator_id
meeting the condition)
In October 2022, find the top 5 creators by total gifts received for each country.
Notes:
gift_recieved_cnt
across all that creator’s rooms in that country during October 2022.
creator_id
ascending (or specify a deterministic tie-break).
Output (recommended):
country
creator_id
total_gifts_received
rank_in_country
(1–5)
For yesterday’s date (relative to query run date), calculate the count of creators who satisfy both:
watch_cnt >= 500
.
Output:
qualified_creator_count