Analyze Streaming Data with SQL for Monthly Trends

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Twitch.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Twitch during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview