cswork / libraryOpen practice workspace

Coding questions · Twitch

Solve advanced SQL for streaming analytics

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.

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.
  1. 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.

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).

Table minute_streamed

columntypeconstraints
time_minuteTIMESTAMPNOT NULL
streamer_usernameVARCHAR(50)NOT NULL
categoryVARCHAR(50)NOT NULL
concurrent_viewersINTNOT NULL

Sample data:

categoryconcurrent_viewersstreamer_usernametime_minute
TTBHGD100aaa2018-12-31 23:59:00
TTBHGD120aaa2019-01-01 00:00:00
TTBHGD130aaa2019-01-01 00:01:00
VGDH50bbb2019-03-15 09:00:00
VGDH60bbb2019-03-15 09:01:00
CCVF70bbb2019-03-31 22:15:00
TTBHGD133aaa2020-03-19 13:00:00
TTBHGD45aaa2020-03-19 13:01:00

Expected output

One row per calendar month with the month label in 'YYYY-MM' format and the total hours streamed across all streamers in that month, rounded to 2 decimal places. Based on the sample data, there are four months with activity: 2018-12, 2019-01, 2019-03, and 2020-03.

hours_streamedmonth_yyyy_mm
0.022018-12
0.032019-01
0.052019-03
0.032020-03

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.

Table minute_streamed

columntypeconstraints
time_minuteTIMESTAMPNOT NULL
streamer_usernameVARCHAR(50)NOT NULL
categoryVARCHAR(50)NOT NULL
concurrent_viewersINTNOT NULL

Sample data:

categoryconcurrent_viewersstreamer_usernametime_minute
TTBHGD100aaa2018-12-31 23:59:00
TTBHGD120aaa2019-01-01 00:00:00
ttbhGD130aaa2019-01-01 00:01:00
VGDH50bbb2019-03-15 09:00:00
VGDH60bbb2019-03-15 09:01:00
CCVF70bbb2019-03-31 22:15:00
TtbhGd133aaa2020-03-19 13:00:00
ARTS45aaa2020-03-19 13:01:00

Expected output

One row per streamer: total streamed minutes, minutes whose category matches 'ttbh' case-insensitively, and that share rounded to 4 decimals; ordered by streamer_username.

keyword_minuteskeyword_sharestreamer_usernametotal_minutes
40.8000aaa5
00.0000bbb3

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.
  3. Cast the numerator to ::numeric (and wrap the denominator in NULLIF(..., 0)) so the division is decimal, not integer.

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).

Table minute_streamed

columntypeconstraints
time_minuteTIMESTAMPNOT NULL
streamer_usernameVARCHAR(50)NOT NULL
categoryVARCHAR(50)NOT NULL
concurrent_viewersINTNOT NULL

Sample data:

categoryconcurrent_viewersstreamer_usernametime_minute
TTBHGD100aaa2018-12-31 23:59:00
TTBHGD120aaa2019-01-01 00:00:00
TTBHGD130aaa2019-01-01 00:01:00
VGDH50bbb2019-03-15 09:00:00
VGDH60bbb2019-03-15 09:01:00
CCVF70bbb2019-03-31 22:15:00
TTBHGD133aaa2020-03-19 13:00:00
TTBHGD45aaa2020-03-19 13:01:00

Expected output

For each streamer-month where streamed minutes are greater than in the immediately preceding calendar month (treating missing months as 0), return the streamer, the month label 'YYYY-MM', the current month minutes, and the previous month minutes. Based on the sample data, each observed month for each streamer is an increase over its previous month (including months where the previous month had 0 minutes).

current_minutesmonth_yyyy_mmprev_minutesstreamer_username
12018-120aaa
22019-011aaa
22020-030aaa
32019-030bbb

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.

Table minute_streamed

columntypeconstraints
time_minuteTIMESTAMPNOT NULL
streamer_usernameVARCHAR(50)NOT NULL
categoryVARCHAR(50)NOT NULL
concurrent_viewersINTNOT NULL

Sample data:

categoryconcurrent_viewersstreamer_usernametime_minute
TTBHGD100aaa2018-12-31 23:59:00
TTBHGD120aaa2019-01-01 00:00:00
TTBHGD130aaa2019-01-01 00:01:00
VGDH50bbb2019-03-15 09:00:00
VGDH60bbb2019-03-15 09:01:00
CCVF70bbb2019-03-31 22:15:00
TTBHGD133aaa2020-03-19 13:00:00
TTBHGD45aaa2020-03-19 13:01:00

Table minute_viewed

columntypeconstraints
time_minuteTIMESTAMPNOT NULL
viewer_usernameVARCHAR(50)NOT NULL
viewer_countryVARCHAR(2)NOT NULL
streamer_usernameVARCHAR(50)NOT NULL

Sample data:

streamer_usernametime_minuteviewer_countryviewer_username
aaa2019-01-01 00:00:00USccc
aaa2019-01-01 00:01:00USccc
bbb2019-03-15 09:00:00JPddd
bbb2019-03-15 09:00:00USeee
bbb2019-03-15 09:01:00USeee
bbb2019-03-31 22:15:00USfff
aaa2020-03-19 13:00:00USccc

Expected output

For each streamer with streamed minutes in 2019, return their average concurrent viewers over those 2019 streamed minutes, the total number of US viewer-minutes in 2019 (count of joined US viewer rows), and the number of distinct streamed minutes in 2019 with at least one US viewer. Based on the sample data, streamer 'aaa' has two 2019 streamed minutes with concurrent_viewers 120 and 130, both with US viewers. Streamer 'bbb' has three 2019 streamed minutes with concurrent_viewers 50, 60, and 70, and three US viewer-minutes.

avg_concurrent_viewers_2019streamer_usernameunique_streaming_minutes_with_us_2019us_viewer_minutes_2019
125aaa22
60bbb33

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.