Write SQL for TikTok Live creator metrics
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
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 distinct `creator_id` meeting 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_cnt` across all that creator’s rooms in that country during October 2022.
- If ties occur at rank 5, break ties by `creator_id` ascending (or specify a deterministic tie-break).
**Output (recommended):**
- `country`
- `creator_id`
- `total_gifts_received`
- `rank_in_country` (1–5)
### Q3 (Hard)
For **yesterday’s date** (relative to query run date), calculate the **count of creators** who satisfy **both**:
1) The creator went live **more than once** that day (i.e., launched **at least 2 rooms**).
2) **Every** Live room they launched that day had **`watch_cnt >= 500`**.
**Output:**
- `qualified_creator_count`
Overview: 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.
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.
Tables
live_room(date DATE, creator_id BIGINT, room_id INT, country VARCHAR(2), live_type VARCHAR(10))
live_room_activity(date DATE, room_id INT, watch_cnt INT, gift_recieved_cnt INT)
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.
Tables
live_room(date DATE, creator_id BIGINT, room_id INT, country VARCHAR(2), live_type VARCHAR(10))
live_room_activity(date DATE, room_id INT, watch_cnt INT, gift_recieved_cnt INT)
Hints
- Join `live_room` to `live_room_activity` on (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:
1) The creator went live more than once on that date.
2) Every Live room they launched that date has watch_cnt >= 500.
Return a single row with the count of such creators.
Tables
live_room(date DATE, creator_id BIGINT, room_id INT, country VARCHAR(2), live_type VARCHAR(10))
live_room_activity(date DATE, room_id INT, watch_cnt INT, gift_recieved_cnt INT)
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).