Twitch Data Scientist Interview Experience — A SQL Screen Where Every Question Got Follow-Up After Follow-Up

Just finished my SQL interview, written on CodePad. It was pretty similar to a question set from the forum, but the interviewer kept hammering me with variant follow-ups on every single question... my brain was practically on fire.

Every row in this table represents one minute, recording what a streamer was doing during that minute:

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

Q1: total monthly hours streamed for each month, in order by month

This one wasn't hard. The interviewer followed up: what if the data isn't all within the same year? I used DATE_FORMAT to pull out both year and month.

Q2: each streamer's total hours streamed, and what percentage of that total is made up of one particular category

There's a variant on this specific category — you need to use LIKE to pull all the data containing the keyword. He followed up with: what if the casing isn't consistent? I said use UPPER to uppercase everything.

Q3: find the streamers who streamed longer this month than last month

For this one I used a self join, joining on a.month = b.month - 1, then set the condition that this month's duration is greater than last month's.

With cte as
(Select streamer_username, month(time_minute), count(*) ct
From minute_streamed
Group by 1,2)

Select streamer_username
From cte a left join cte b on a.month=b.month-1
Where b.ct>a.ct
Group by streamer_username

He followed up on this one again: what if the table spans different years, like Dec 2018 | 9 and Jan 2019 | 12? I said you could use DATE_SUB, DATE_ADD, or DATE_DIFF, and then he had me actually write it out.

Then one more follow-up: what if it's null in Feb and 9 in Mar — the streamer didn't stream in February but did in March, and that also counts as an increase, so how do you handle it? I used IFNULL to turn all the nulls into 0.

Q4:

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

The first table is the same one used in the first three questions. The second table is a viewer table, where each row is what a viewer was watching during that minute.

The question: each streamer's average concurrent viewers in 2019, and the total hours they were watched by viewers from the US in 2019.

By the time I got to writing this one, time was almost up and I was a mess. Here's my code — can folks take a look and tell me if it's right:

select s.streamer_username, avg(s.concurrent_viewers), sum(case when v.viewer_country='US' then 1 else 0 end) u_viewer
from minute_streamed s join minute_viewed v on s.streamer_username=v.streamer_username
where year(s.time_minute)='2019'
group by s.streamer_username

Follow-up question: does AVG execute first or does the JOIN execute first... My brain was so scrambled from nerves at that point that I just blurted out "AVG first"... Thinking about it now, that's wrong... I'm such an idiot...