Coding questions · TikTok
Tiktok DS Interview Questions
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.
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:
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.
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 asYYYY-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.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR(50) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| concurrent_viewers | INT | NOT NULL |
Sample data:
| category | concurrent_viewers | streamer_username | time_minute |
|---|---|---|---|
| TTBHGD | 133 | aaa | 2020-03-19 13:00:00 |
| TTBHGD | 45 | aaa | 2020-03-19 13:01:00 |
| VGDH | 129 | bbb | 2020-03-20 21:01:00 |
| CCVF | 17 | bbb | 2020-03-30 22:15:00 |
| VGDH | 88 | ccc | 2021-01-05 09:30:00 |
| VGDH | 90 | ccc | 2021-01-05 09:31:00 |
| CCVF | 12 | ddd | 2021-01-05 09:32:00 |
Expected output
One row per calendar month: total streamed minutes and the equivalent hours (minutes/60, rounded to 4 dp), ordered by year_month.
| streamed_hours | streamed_minutes | year_month |
|---|---|---|
| 0.0667 | 4 | 2020-03 |
| 0.0500 | 3 | 2021-01 |
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.
- Cast the count to numeric before dividing by 60, or integer division will truncate to 0.
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.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | DATETIME | NOT NULL, PRIMARY KEY (time_minute, streamer_username) |
| streamer_username | VARCHAR(50) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| concurrent_viewers | INT | NOT NULL |
Sample data:
| category | concurrent_viewers | streamer_username | time_minute |
|---|---|---|---|
| TTBHGD | 133 | aaa | 2020-03-19 13:00:00 |
| TTBHGD | 45 | aaa | 2020-03-19 13:01:00 |
| VGDH | 129 | bbb | 2020-03-20 21:01:00 |
| CCVF | 17 | bbb | 2020-03-30 22:15:00 |
Expected output
Uses case-insensitive equality on the provided category. With category_param = 'TTBHGD', streamer 'aaa' has ratio 1.0 and 'bbb' has 0.0.
| category_minutes | category_ratio | streamer_username | total_minutes |
|---|---|---|---|
| 2 | 1 | aaa | 2 |
| 0 | 0 | bbb | 2 |
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.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR(50) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| concurrent_viewers | INT | NOT NULL |
Sample data:
| category | concurrent_viewers | streamer_username | time_minute |
|---|---|---|---|
| TTBHGD | 133 | aaa | 2020-11-10 13:00:00 |
| TTBHGD | 45 | aaa | 2020-11-10 13:01:00 |
| TTBHGD | 80 | aaa | 2020-12-05 09:00:00 |
| TTBHGD | 70 | aaa | 2020-12-05 09:01:00 |
| TTBHGD | 60 | aaa | 2020-12-05 09:02:00 |
| VGDH | 22 | aaa | 2021-01-02 18:00:00 |
| VGDH | 129 | bbb | 2020-12-20 21:01:00 |
| VGDH | 17 | bbb | 2020-12-20 21:02:00 |
Expected output
One row per streamer-month whose minute count exceeds the same streamer's immediately-previous calendar month (missing prior month counts as 0).
| minutes_streamed | month_start | prev_minutes | streamer_username |
|---|---|---|---|
| 2 | 2020-11-01 | 0 | aaa |
| 3 | 2020-12-01 | 2 | aaa |
| 2 | 2020-12-01 | 0 | bbb |
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.
- LEFT JOIN plus COALESCE(prev, 0) lets a month after a gap still qualify.
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.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | DATETIME | NOT NULL, PRIMARY KEY (time_minute, streamer_username) |
| streamer_username | VARCHAR(50) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| concurrent_viewers | INT | NOT NULL |
Sample data:
| category | concurrent_viewers | streamer_username | time_minute |
|---|---|---|---|
| TTBHGD | 133 | aaa | 2020-03-19 13:00:00 |
| TTBHGD | 45 | aaa | 2020-03-19 13:01:00 |
| VGDH | 129 | bbb | 2020-03-20 21:01:00 |
| CCVF | 17 | bbb | 2020-03-30 22:15:00 |
Table minute_viewed
| column | type | constraints |
|---|---|---|
| time_minute | DATETIME | NOT NULL, PRIMARY KEY (time_minute, viewer_username, streamer_username) |
| viewer_username | VARCHAR(50) | NOT NULL |
| viewer_country | CHAR(2) | NOT NULL |
| streamer_username | VARCHAR(50) | NOT NULL |
Sample data:
| streamer_username | time_minute | viewer_country | viewer_username |
|---|---|---|---|
| aaa | 2020-03-19 13:00:00 | US | ccc |
| aaa | 2020-03-19 13:01:00 | US | ccc |
| aaa | 2020-03-20 21:01:00 | JP | ddd |
| aaa | 2020-03-30 22:15:00 | JP | ddd |
Expected output
Correctly separates computations to avoid duplicating streamed rows when counting viewer minutes. The sample has no 2019 data, so the result is empty.
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.