Summary
Twitch data scientist interview that was basically a full SQL gauntlet built around streaming telemetry data. Five tasks back to back, ranging from monthly aggregations to window functions to a conceptual question about query processing order. Felt more like a take-home than a live screen.
Questions Asked(5)
The year-boundary thing is what trips people up.
Suggested Approach
Start by identifying the granularity of the source data (minute-level rows per streamer) and clarify what constitutes a 'streaming minute' to avoid double-counting. Then aggregate by extracting the year-month from the timestamp, summing total minutes across all streamers, and converting to hours. Finally, ensure the output is sorted chronologically using the YYYY-MM label, not lexicographically on a raw string.
Clarify the Schema & Grain
Confirm the source table structure — e.g., columns like streamer_id, timestamp, and whether each row represents one minute of streaming or a session with a duration field. Clarifying this prevents logic errors in the aggregation.
Extract the YYYY-MM Period
Use DATE_FORMAT(timestamp, '%Y-%m') or equivalent (e.g., TO_CHAR in PostgreSQL, FORMAT_DATE in BigQuery) to derive the month label. Avoid casting to just MONTH() alone, as that collapses data across multiple years.
Aggregate Minutes and Convert to Hours
SUM the minute-level rows grouped by the YYYY-MM period to get total minutes, then divide by 60 to convert to hours. Consider whether to ROUND or keep decimals based on business requirements.
Sort Chronologically
Order the results by the raw timestamp truncated to month (e.g., DATE_TRUNC('month', timestamp)) or by the YYYY-MM string, which sorts correctly due to its ISO format. Avoid ordering by a plain MONTH() integer, which breaks across years.
Validate & Handle Edge Cases
Check for NULL timestamps, duplicate rows, or timezone inconsistencies (e.g., UTC vs. local time) that could misassign minutes to the wrong month. Mention that at Twitch's scale, late-arriving data or reprocessing windows may also need to be addressed.
Key Points to Mention
The case-insensitive part is easy to miss if you just do category = cat_keyword.
Suggested Approach
Break the problem into two aggregation layers: first compute total streamed minutes per streamer, then compute keyword-matched minutes per streamer using a case-insensitive filter, and finally join the two to derive the ratio. Use SQL window functions or CTEs to keep the logic clean and readable, which is especially important in a data-heavy company like Twitch.
Clarify the Schema and Inputs
Confirm the table structure (e.g., streamer_id, category, minutes_streamed) and how the keyword is passed — as a parameter or hardcoded. Ask whether a streamer can appear in multiple rows with different categories.
Compute Total Minutes per Streamer
Use a GROUP BY on streamer_id with SUM(minutes_streamed) to get each streamer's total minutes. Store this in a CTE for reuse.
Filter and Aggregate Keyword-Matched Minutes
In a second CTE, apply a case-insensitive substring match (e.g., LOWER(category) LIKE LOWER('%keyword%') or ILIKE in PostgreSQL) and SUM the matching minutes per streamer.
Join and Compute the Ratio
LEFT JOIN the keyword-matched CTE onto the total-minutes CTE so streamers with no matching categories still appear. Divide keyword minutes by total minutes, using COALESCE to handle NULLs and NULLIF to prevent division by zero.
Validate and Discuss Edge Cases
Walk through edge cases: streamers with zero total minutes, NULL category values, and keywords that match no rows. Mention that the ratio should be bounded between 0 and 1 as a sanity check.
Key Points to Mention
This one took me a minute.
Suggested Approach
Use a window function (LAG) partitioned by streamer and ordered by calendar month to fetch the previous month's streamed hours, while using a complete month spine or COALESCE to handle months with no activity as zero. Then filter the result set to only include rows where the current month's hours exceed the previous month's hours. This approach cleanly handles gaps in the data and avoids complex self-joins.
Clarify Requirements & Edge Cases
Confirm the definition of 'calendar month' (truncated date vs. year-month integer), the grain of the source data (event-level vs. pre-aggregated), and whether the very first month for a streamer should be included if it has activity (previous month would be zero).
Aggregate Hours by Streamer and Month
Group the raw streaming data by streamer_id and calendar month (using DATE_TRUNC or equivalent), summing streamed hours to get a monthly total per streamer.
Build a Complete Month Spine
Generate all (streamer, month) combinations across the full date range and LEFT JOIN the aggregated data onto this spine, replacing NULLs with zero using COALESCE to correctly represent inactive months.
Apply LAG Window Function
Use LAG(hours, 1, 0) OVER (PARTITION BY streamer_id ORDER BY month) to retrieve the previous calendar month's hours for each row, defaulting to zero if no prior row exists.
Filter and Return Results
Wrap the windowed query in a CTE or subquery and apply a WHERE clause to retain only rows where current_month_hours > previous_month_hours, then select the relevant output columns.
Key Points to Mention
The join-on-both-columns instruction is a hint that they've seen people mess this up by joining only on streamer and getting a cartesian product on time.
Suggested Approach
Start by identifying the two core tables — minute_streamed (one row per streamer per broadcast minute) and minute_viewed (one row per viewer per streamer per minute) — and join them on both streamer and timestamp before aggregating to prevent a Cartesian explosion. Filter to 2019 at the earliest possible stage, then compute concurrent viewers as the average across all streamed minutes and US viewer-minutes as the sum of US viewer rows (each row representing one viewer-minute). For the follow-up, add a conditional COUNT DISTINCT on timestamp where US viewers exist.
Clarify Table Schemas & Grain
Confirm that minute_streamed has grain (streamer, timestamp) and minute_viewed has grain (viewer_id, streamer, timestamp), and identify which columns carry concurrent_viewers and US viewer flags. This prevents assumptions that lead to incorrect joins.
Filter Early for 2019
Apply a WHERE clause filtering timestamp to the year 2019 on both tables before or within the join to minimize data scanned and avoid carrying unnecessary rows into aggregation.
Join on Both Keys to Prevent Row Multiplication
Join minute_streamed to minute_viewed on streamer AND timestamp together; joining on only one key would create a cross-product between minutes and viewers, inflating all downstream metrics.
Aggregate Core Metrics
Group by streamer and compute AVG(concurrent_viewers) from the streamed side for average concurrent viewers, and SUM of a US-viewer indicator (e.g., SUM(CASE WHEN country = 'US' THEN 1 ELSE 0 END)) for total US viewer-minutes.
Add Follow-Up: Distinct Streamed Minutes with US Viewers
Extend the query with COUNT(DISTINCT CASE WHEN country = 'US' THEN ms.timestamp END) per streamer, which counts only the unique broadcast minutes during which at least one US viewer was present.
Key Points to Mention
AVG happens after the JOIN, in the GROUP BY and SELECT phase.
Suggested Approach
Start by clearly enumerating SQL's logical processing order from FROM through SELECT, then precisely locate where aggregate functions like AVG() fall in that sequence relative to JOINs. Ground your explanation in a concrete, Twitch-relevant example (e.g., average watch time per streamer after joining a users table) to make the answer tangible and demonstrate applied understanding.
State the Full Logical Processing Order
Enumerate all clauses in logical order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. Be explicit and sequential to show command of the full pipeline.
Pinpoint WHERE AVG() Is Evaluated
Clarify that AVG() is an aggregate function resolved during the GROUP BY/aggregation phase (step 5 logically), which occurs after FROM and JOIN (steps 1-2) have already produced the combined result set.
Explain the JOIN → Aggregate Relationship
Emphasize that the JOIN first expands or filters the row set, and AVG() then operates on that already-joined dataset — meaning the join condition directly influences which rows are included in the average calculation.
Illustrate with a Concrete Example
Use a Twitch-relevant scenario, such as joining a 'streams' table with a 'channels' table and computing AVG(viewer_count) per category, to show how an INNER JOIN vs. LEFT JOIN would yield different AVG() results because the row set differs.
Address Common Pitfalls and HAVING vs. WHERE
Note that because AVG() is computed after WHERE but before HAVING, you cannot filter on an aggregate in a WHERE clause — you must use HAVING — which is a direct consequence of the logical processing order.
Key Points to Mention
Discussion(5)
Sign in to join the discussion.
The spine approach is the right call here. LAG over a sparse result set is one of those things that looks correct until you hit a streamer who took a month off, at which point LAG happily grabs two months back and you never notice. Generating the spine with a cross join between a distinct streamer list and a distinct month list, then left joining your aggregated data onto it and coalescing nulls to zero, gives you a complete grid before the window function ever runs. After that LAG works exactly as you'd expect. One small thing: if you're sorting by a YYYY-MM string make sure it's zero-padded (2019-01 not 2019-1) otherwise lexicographic order breaks. DATE_TRUNC output avoids this entirely since it's a proper timestamp.
NULLIF on the denominator is a good reflex to build. I once got burned in a take-home where the test data was clean but the grader ran it against a dataset with zero-minute streamers and my query threw a division error. Embarrassing. For the substring match, LOWER(category) LIKE LOWER('%' || cat_keyword || '%') is portable across most dialects if ILIKE isn't available. The ratio itself is just SUM(CASE WHEN ... THEN minutes ELSE 0 END) / NULLIF(SUM(minutes), 0). Keep the numerator filter and the denominator on the same grain so you're not accidentally mixing row counts with minute counts.
Joining on both streamer and timestamp is the whole ballgame here. I made exactly this mistake once, joined only on streamer ID, and suddenly my average concurrent viewers was off by an order of magnitude because every viewer row was fanning out against every streamed-minute row for that streamer. The follow-up about distinct streamed minutes with at least one US viewer is sneaky because it sounds like a viewer-side count but it's actually a streamer-side count. You want COUNT(DISTINCT ms.time_minute) after filtering mv.viewer_country = 'US', which means the join needs to be structured so you're anchoring on minute_streamed and bringing in viewer data, not the other way around. If you anchor on minute_viewed you'll end up counting viewer rows not stream minutes.
FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. AVG runs in the SELECT phase, so the join is already done by then.
The year-boundary thing is a real gotcha and I've watched people confidently write MONTH() and GROUP BY MONTH() in live screens without realizing they're collapsing multiple years together. The fix is just making your grouping key carry the year too, either via DATE_TRUNC to a month-level timestamp or a formatted string like TO_CHAR(time_minute, 'YYYY-MM'). One thing worth flagging for Twitch specifically: if the data is stored in UTC and streamers are global, you might get a follow-up about timezone handling at year boundaries, like a stream that started at 11:58 PM December 31 UTC. Probably not in scope for a phone screen but good to have in your back pocket. The divide-by-60.0 (not 60) matters too since integer division will silently truncate in some dialects.