Tiktok DS Interview Questions
Company: TikTok
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Scenario:
You are provided with two tables: `minute_streamed` and `minute_viewed`. The `minute_streamed` table records each minute of streaming activity, while the `minute_viewed` table records each minute of viewer activity.
`minute_streamed` table structure:
+---------------------+-------------------+----------+--------------------+
| time_minute | streamer_username | category | concurrent_viewers |
+---------------------+-------------------+----------+--------------------+
| 2020-03-19 13:00:00 | aaa | TTBHGD | 133 |
| 2020-03-19 13:01:00 | aaa | TTBHGD | 45 |
| 2020-03-20 21:01:00 | bbb | VGDH | 129 |
| 2020-03-30 22:15:00 | bbb | CCVF | 17 |
+---------------------+-------------------+----------+--------------------+
`minute_viewed` table structure:
+---------------------+-----------------+----------------+-------------------+
| time_minute | viewer_username | viewer_country | streamer_username |
+---------------------+-----------------+----------------+-------------------+
| 2020-03-19 13:00:00 | ccc | US | aaa |
| 2020-03-19 13:01:00 | ccc | US | aaa |
| 2020-03-20 21:01:00 | ddd | JP | aaa |
| 2020-03-30 22:15:00 | ddd | JP | aaa |
+---------------------+-----------------+----------------+-------------------+
Questions:
Q1: Calculate the total streamed hours for each month.
Write a query to sum the streamed minutes for each month and convert it to hours.
Include a solution for when data spans multiple years.
Q2: Determine the total streaming duration of each streamer along with the ratio of a specific category to the total duration.
Describe how to handle queries when the category varies using keywords and case sensitivity.
Q3: Identify streamers who streamed more in a given month compared to the previous month.
Outline how to deal with different years and handle cases where no data is available for a month (e.g., NULL values).
Q4: Compute the average concurrent viewers for each streamer in 2019 and the total view time from US viewers.
Use the provided code for reference and evaluate its correctness:
```sql
SELECT s.streamer_username, AVG(s.concurrent_viewers) AS avg_viewers, SUM(CASE WHEN v.viewer_country = 'US' THEN 1 ELSE 0 END) AS us_viewer_time
FROM minute_streamed s
JOIN minute_viewed v ON s.streamer_username = v.streamer_username
WHERE YEAR(s.time_minute) = '2019'
GROUP BY s.streamer_username
Hints:
Consider how to format dates and handle string operations in SQL.
Use window functions if beneficial for solving part of the problem.
Explore using conditional statements or arithmetic to handle NULL values and different yearly datasets.
Overview: This question evaluates proficiency in SQL-based time-series aggregation, joins between streaming and viewing tables, conditional and null-aware aggregations, string and case-insensitive category filtering, and basic analytical functions used in data science workflows.
Monthly streamed hours
You are given the `minute_streamed` table, where **each row represents exactly one minute that a streamer was live**. The relevant columns are:
- `time_minute` (TIMESTAMP) — the minute that was streamed.
- `streamer_username` (VARCHAR) — the streamer who was live during that minute.
- `category` (VARCHAR) — the stream category.
- `concurrent_viewers` (INT) — viewers during that minute.
Write a query that aggregates the **total number of streamed minutes per calendar month** across **all** streamers and also converts that total into hours. The data may span multiple years, so months from different years must be reported separately (do not collapse, e.g., March 2020 and March 2021 into one row).
Return exactly three columns:
- `year_month` — the calendar month formatted as `YYYY-MM` (e.g. `2020-03`).
- `streamed_minutes` — the count of streamed-minute rows in that month.
- `streamed_hours` — `streamed_minutes / 60`, rounded to 4 decimal places.
Sort the result by `year_month` in ascending order.
Tables
minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)
Hints
- Each row is one streamed minute, so COUNT(*) per group gives the streamed minutes.
- Use TO_CHAR(time_minute, 'YYYY-MM') to bucket by month while keeping years distinct.
Streamer category ratio
For each streamer, compute total streamed minutes, minutes in a specified category (using a case-insensitive match on the category name), and the ratio category_minutes/total_minutes.
Tables
minute_streamed(time_minute DATETIME, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)
Hints
- Use UPPER() or LOWER() for case-insensitive comparison.
- Compute ratio as SUM(condition)/COUNT(*).
Month-over-month increases
## Month-over-Month Streaming Increases
The `minute_streamed` table logs one row per minute that a streamer is live. Each row records the `time_minute` timestamp, the `streamer_username`, the broadcast `category`, and the `concurrent_viewers` count at that minute.
For every `(streamer, calendar-month)` pair, define **minutes streamed** as the number of rows that streamer has in that month. Identify the streamer-months where the minutes streamed are **strictly greater** than the minutes the **same** streamer accumulated in the **immediately previous calendar month**.
Rules:
- If the streamer had no activity in the previous calendar month (i.e. the previous month is missing), treat the previous-month total as **0** — so any month with activity following a gap qualifies.
- "Immediately previous calendar month" must respect year boundaries: the month before January 2021 is December 2020.
- A month qualifies only when its total is **strictly greater** than the previous month's total (equal totals do not qualify).
### Required output
Return one row per qualifying streamer-month with these columns, in this order:
| column | meaning |
|---|---|
| `streamer_username` | the streamer |
| `month_start` | first day of the qualifying month, formatted `YYYY-MM-DD` |
| `minutes_streamed` | rows that streamer has in that month |
| `prev_minutes` | rows that streamer had in the immediately previous calendar month (0 if missing) |
Sort the result by `streamer_username` ascending, then by `month_start` ascending.
Tables
minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)
Hints
- First roll the per-minute rows up to one count per streamer per month with DATE_TRUNC('month', time_minute).
- Self-join each month to its predecessor using (month_start - INTERVAL '1 month')::date so year boundaries are handled.
2019 averages and US minutes
For the year 2019, compute per-streamer average concurrent viewers (from minute_streamed) and total US viewer minutes (from minute_viewed). Ensure the join logic does not distort the average concurrent viewers.
Tables
minute_streamed(time_minute DATETIME, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)
minute_viewed(time_minute DATETIME, viewer_username VARCHAR(50), viewer_country CHAR(2), streamer_username VARCHAR(50))
Hints
- Avoid joining minute_viewed to minute_streamed before averaging; it duplicates streamed rows.
- Compute the average and the US minutes in separate aggregates, then join.
Community answers
Answer by yoyo
part 1:
SELECT DATE_FORMAT(time_minute, '%Y-%m') AS year_month, COUNT() AS streamed_minutes, ROUND(COUNT() / 60, 4) AS streamed_hoursFROM minute_streamedGROUP BY DATE_FORMAT(time_minute, '%Y-%m')ORDER BY year_month;
part 2:
SELECT
streamer_username,
COUNT(*) AS total_minutes,
SUM(CASE WHEN UPPER(category) = UPPER('TTBHGD') THEN 1 ELSE 0 END) AS category_minutes,
ROUND(
SUM(CASE WHEN UPPER(category) = UPPER('TTBHGD') THEN 1 ELSE 0 END) / COUNT(*),
4
) AS category_ratio
FROM minute_streamed
GROUP BY streamer_username
ORDER BY streamer_username;
part 3:
with cte as (SELECT streamer_username, date_format(time_minute, '%y-%m') as year_month, count(*) as time_cnt FROM minute_streamedgroup by streamer_username, date_format(time_minute, '%y-%m'))
with tmp as (select streamer_username, ifnull(lag(time_cnt) over( partition by streamer_username order by year_month asc),0) as prev_month, time_cntfrom cte)
select streamer_usernamefrom tmp where prev_month < time_cnt;
part 4:
-- Write your SQL query herewith stream_2019 as (SELECT streamer_username, avg(concurrent_viewers) as viewer_cnts FROM minute_streamed where year(time_minute) = 2019group by streamer_username),
us_views_2019 as ( select streamer_username, count(*) as us_viewers_2019 from minute_viewed where year(time_minute) =2019 and viewer_country = 'US' group by streamer_username),
select streamer_username, a.viewer_cnts, b.us_viewers_2019from stream_2019 a left join us_views_2019 b on a.streamer_username = b.streamer_username