Coding questions · Twitch
Analyze Streaming Data with SQL for Monthly Trends
This question evaluates proficiency in SQL time-series aggregation, date extraction and grouping across calendar months, join and conditional aggregation techniques, windowed month-over-month comparisons, and handling sparse or multi-year datasets; Category: Data Manipulation (SQL/Python); level: practical application.
minute_streamed
+---------------------+------------------+------------+--------------------+
| 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
+---------------------+------------------+---------------+------------------+
| 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 |
+---------------------+------------------+---------------+------------------+
Scenario
You work for a live-streaming platform that stores one record per streamed minute and one record per viewed minute. Management wants several analytics reports over these tables.
Question
From table minute_streamed, compute the total hours streamed for every calendar month; return results ordered chronologically. Explain how your query still works when the data spans multiple years. 2. For each streamer, return (a) their total streamed hours and (b) the percentage of those hours that belong to a category containing a given keyword (case-insensitive match). 3. List all streamers whose total streamed hours in any month are greater than the previous month. Handle datasets that contain more than one year and months with no activity. 4. Using minute_streamed and minute_viewed, produce for each streamer (a) their average concurrent_viewers in 2019 and (b) the total minutes they were watched by viewers from the US in 2019.
Hints
Use EXTRACT or DATE_FORMAT to pull year/month, COUNT(*)/60 to convert minutes to hours, UPPER(...) LIKE, IFNULL for missing months, and self-join or window functions for month-over-month comparisons.
Monthly hours streamed
From minute_streamed, compute total hours streamed for each calendar year-month across all years. Make sure your query works correctly when the data spans multiple years. Return columns year, month, and hours_streamed, ordered chronologically.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR | NOT NULL |
| category | VARCHAR | NOT NULL |
| concurrent_viewers | INTEGER | 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
One row per year-month across all years with total hours streamed (minutes/60).
| hours_streamed | month | year |
|---|---|---|
| 0.0667 | 3 | 2020 |
Hints
- Group by EXTRACT(YEAR) and EXTRACT(MONTH).
- Each row represents one minute; convert minutes to hours by dividing by 60.
Keyword category share
For each streamer, compute total streamed hours and the percentage of those hours in categories whose name contains a provided keyword (case-insensitive). Return streamer_username, total_hours_streamed, keyword_category_hours_pct. Assume the keyword is provided; for the sample output use 'TTB'.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR | NOT NULL |
| category | VARCHAR | NOT NULL |
| concurrent_viewers | INTEGER | 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
Per-streamer totals and percentage of minutes in categories matching the keyword (case-insensitive).
| keyword_category_hours_pct | streamer_username | total_hours_streamed |
|---|---|---|
| 100 | aaa | 0.0333 |
| 0 | bbb | 0.0333 |
Hints
- Compare categories using UPPER(...) and LIKE with wildcards.
- Compute minutes per streamer, then divide by 60 for hours.
- Protect against division by zero with CASE/COALESCE.
Month-over-month increases
List all streamers who have any month where their total streamed minutes are greater than the previous month. Between a streamer’s first and last active months, treat missing months as zero. Return distinct streamer_username ordered alphabetically.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR | NOT NULL |
| category | VARCHAR | NOT NULL |
| concurrent_viewers | INTEGER | 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
Distinct streamers with at least one month where minutes increased versus the immediately previous month after zero-filling missing months.
Hints
- Aggregate minutes per streamer per month with date_trunc('month', ...).
- Generate a complete month series per streamer and left join to zero-fill.
- Use LAG to compare with the previous month.
2019 averages and US minutes
Using minute_streamed and minute_viewed, for each streamer present in either table, return their average concurrent_viewers in 2019 and the total minutes they were watched by US viewers in 2019. If a metric is missing, return 0.
Table minute_streamed
| column | type | constraints |
|---|---|---|
| time_minute | TIMESTAMP | NOT NULL |
| streamer_username | VARCHAR | NOT NULL |
| category | VARCHAR | NOT NULL |
| concurrent_viewers | INTEGER | 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 | TIMESTAMP | NOT NULL |
| viewer_username | VARCHAR | NOT NULL |
| viewer_country | VARCHAR(2) | NOT NULL |
| streamer_username | VARCHAR | 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
For each streamer in either table, the average concurrent viewers in 2019 and total minutes watched by US viewers in 2019. Missing values default to 0.
| avg_concurrent_viewers_2019 | streamer_username | us_minutes_watched_2019 |
|---|---|---|
| 0 | aaa | 0 |
| 0 | bbb | 0 |
Hints
- Build the full streamer list with a UNION of both tables.
- Filter 2019 with a half-open date range.
- COUNT rows in minute_viewed to get minutes watched.