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...
Discussion
Loading comments…