Quick Overview

This question evaluates advanced SQL competency for streaming analytics — covering time-series aggregation, monthly bucketing across year boundaries, joins between event tables, window functions, and case-insensitive substring filtering; the domain is Data Manipulation (SQL/Python) and the level is practical application of SQL techniques.

Solve advanced SQL for streaming analytics

Company: Twitch

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

You are given minute-level streaming telemetry. Each row in minute_streamed represents one minute during which a streamer was live; each row in minute_viewed represents one viewer watching a streamer for one minute. Schema - minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR, category VARCHAR, concurrent_viewers INT) - minute_viewed(time_minute TIMESTAMP, viewer_username VARCHAR, viewer_country VARCHAR, streamer_username VARCHAR) Sample data (small and illustrative) minute_streamed +---------------------+------------------+----------+--------------------+ | time_minute | streamer_username| category | concurrent_viewers | +---------------------+------------------+----------+--------------------+ | 2018-12-31 23:59:00 | aaa | TTBHGD | 100 | | 2019-01-01 00:00:00 | aaa | TTBHGD | 120 | | 2019-01-01 00:01:00 | aaa | TTBHGD | 130 | | 2019-03-15 09:00:00 | bbb | VGDH | 50 | | 2019-03-15 09:01:00 | bbb | VGDH | 60 | | 2019-03-31 22:15:00 | bbb | CCVF | 70 | | 2020-03-19 13:00:00 | aaa | TTBHGD | 133 | | 2020-03-19 13:01:00 | aaa | TTBHGD | 45 | +---------------------+------------------+----------+--------------------+ minute_viewed +---------------------+----------------+----------------+------------------+ | time_minute | viewer_username| viewer_country | streamer_username| +---------------------+----------------+----------------+------------------+ | 2019-01-01 00:00:00 | ccc | US | aaa | | 2019-01-01 00:01:00 | ccc | US | aaa | | 2019-03-15 09:00:00 | ddd | JP | bbb | | 2019-03-15 09:00:00 | eee | US | bbb | | 2019-03-15 09:01:00 | eee | US | bbb | | 2019-03-31 22:15:00 | fff | US | bbb | | 2020-03-19 13:00:00 | ccc | US | aaa | +---------------------+----------------+----------------+------------------+ Assumptions - A streamer may switch categories at any minute. - Months are calendar months based on time_minute (UTC). Label months as YYYY-MM. - 1 row in minute_streamed = 1 minute streamed; hours = minutes/60. - You may use ANSI SQL with CTEs and window functions. Tasks 1) Compute total monthly hours streamed for each month across all streamers. Output (month_yyyy_mm, hours_streamed). Order chronologically. Ensure it works when data spans multiple years (e.g., 2018-12 and 2019-01). 2) For each streamer, return their total streamed minutes and the share accounted for categories whose name contains a given keyword, case-insensitive. Input parameter: cat_keyword VARCHAR. Output (streamer_username, total_minutes, keyword_minutes, keyword_share = keyword_minutes/total_minutes). Treat category matching as case-insensitive substring search. 3) Find, for each streamer and month, whether their streamed hours increased versus the immediately previous calendar month. Treat a missing previous month as 0 minutes (so if a streamer did not stream in February but streamed in March, March counts as an increase over February=0). Handle year boundaries correctly (e.g., compare 2019-01 to 2018-12). Output rows only for months where current_month_minutes > prev_month_minutes: (streamer_username, month_yyyy_mm, current_minutes, prev_minutes). 4) For the year 2019 only, return for each streamer: - avg_concurrent_viewers_2019: the average of concurrent_viewers over that streamer's streamed minutes in 2019 (use minute_streamed only). - us_viewer_minutes_2019: the total number of viewer-minutes from US viewers in 2019. Join minute_viewed to minute_streamed on both (streamer_username, time_minute) so you don’t multiply counts. Output (streamer_username, avg_concurrent_viewers_2019, us_viewer_minutes_2019). As a follow-up, also compute unique_streaming_minutes_with_us_2019: the count of distinct streamed minutes in 2019 where the streamer had at least one US viewer. 5) Conceptual: Briefly explain SQL’s logical query processing order (FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY) and answer: logically, does AVG() happen before or after JOIN? Deliverables - Provide SQL for tasks 1–4 and a 1–2 sentence explanation for task 5. Ensure your queries are robust to mixed-case categories and months with no activity.

Overview: This question evaluates advanced SQL competency for streaming analytics — covering time-series aggregation, monthly bucketing across year boundaries, joins between event tables, window functions, and case-insensitive substring filtering; the domain is Data Manipulation (SQL/Python) and the level is practical application of SQL techniques.

Read the full Twitch Data Scientist interview experience this question came from

Monthly Total Hours Streamed Across All Streamers

Using the minute_streamed table, compute the total monthly hours streamed for each calendar month across all streamers. Each row in minute_streamed represents one minute of streaming time. Label each month as 'YYYY-MM' based on time_minute (UTC), and compute hours_streamed as total minutes streamed divided by 60, rounded to 2 decimal places. Return columns (month_yyyy_mm, hours_streamed) ordered chronologically. Ensure the query works when data spans multiple years (e.g., 2018-12 and 2019-01).

Tables

minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)

Hints

  1. Group by a month key derived from time_minute, such as TO_CHAR(time_minute, 'YYYY-MM') or a truncated month date.
  2. Count minutes per month and divide by 60.0 to get hours; use ROUND to control decimal places.

Streamer Category Keyword Share of Minutes

Using the `minute_streamed` table (one row per minute a streamer was live), compute, for each streamer, what fraction of their streamed minutes were spent in categories matching a keyword. The keyword is fixed to **`ttbh`** for this exercise, and category matching is a **case-insensitive substring** match — a minute counts toward the keyword if `LOWER(category) LIKE '%ttbh%'` (so `TTBHGD`, `ttbhGD`, and `TtbhGd` all match). The query must be robust to mixed-case `category` values. Return exactly one row per streamer with these columns: - `streamer_username` — the streamer - `total_minutes` — total number of streamed minutes (rows) for that streamer - `keyword_minutes` — number of those minutes whose category matches the keyword `ttbh` (case-insensitive) - `keyword_share` — `keyword_minutes / total_minutes`, as a decimal rounded to 4 places Order the result by `streamer_username` ascending.

Tables

minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)

Hints

  1. Use LOWER(category) LIKE '%' || LOWER('ttbh') || '%' so the substring match ignores case.
  2. Compute keyword_minutes with SUM(CASE WHEN ... THEN 1 ELSE 0 END), then divide by COUNT(*) for the share.

Monthly Increase in Streamed Minutes per Streamer

Using the minute_streamed table, find, for each streamer and calendar month, whether their streamed minutes increased versus the immediately previous calendar month. Treat each row in minute_streamed as one minute streamed. A missing previous month for a streamer should be treated as having 0 minutes (e.g., if a streamer did not stream in February but streamed in March, compare March against February = 0). Handle year boundaries correctly (e.g., compare 2019-01 to 2018-12). Return rows only for months where current_month_minutes > prev_month_minutes with columns (streamer_username, month_yyyy_mm, current_minutes, prev_minutes), ordered by streamer_username and month. Label months as 'YYYY-MM' based on time_minute (UTC).

Tables

minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)

Hints

  1. First aggregate to minutes per streamer per month using date_trunc('month', time_minute).
  2. Self-join the monthly aggregates to their prior month using month_start - INTERVAL '1 month', COALESCE missing previous rows to 0, then filter where current_minutes > prev_minutes.

2019 Streaming Averages and US Viewer Minutes per Streamer

Using both minute_streamed and minute_viewed, for the year 2019 only, return for each streamer: (1) avg_concurrent_viewers_2019: the average of concurrent_viewers over that streamer's streamed minutes in 2019 (using minute_streamed only); (2) us_viewer_minutes_2019: the total number of viewer-minutes from US viewers in 2019; (3) unique_streaming_minutes_with_us_2019: the count of distinct streamed minutes in 2019 where the streamer had at least one US viewer. Treat each row in minute_streamed as one streamed minute and each row in minute_viewed as one viewer-minute. To compute viewer metrics, join minute_viewed to minute_streamed on (streamer_username, time_minute) and filter viewer_country = 'US'. Restrict all metrics to stream minutes in 2019 (UTC). Output columns (streamer_username, avg_concurrent_viewers_2019, us_viewer_minutes_2019, unique_streaming_minutes_with_us_2019), ordered by streamer_username.

Tables

minute_streamed(time_minute TIMESTAMP, streamer_username VARCHAR(50), category VARCHAR(50), concurrent_viewers INT)

minute_viewed(time_minute TIMESTAMP, viewer_username VARCHAR(50), viewer_country VARCHAR(2), streamer_username VARCHAR(50))

Hints

  1. First, isolate 2019 streamed minutes, compute AVG(concurrent_viewers) per streamer from minute_streamed.
  2. For US viewer metrics, join filtered minute_viewed (viewer_country = 'US') to the 2019 streamed minutes, then aggregate COUNT(*) for viewer-minutes and COUNT(DISTINCT time_minute) for distinct streaming minutes with at least one US viewer.

Loading coding console...