Twitch Product Analyst | October 2020

Company: twitch

Position: Product Analyst

Location: nan

Level: nan

Outcome: NA

How was the interview process? What was it like?

No

What technical questions were asked?

nan

What was one of your solutions?

-- 1: Write a query that returns total monthly hours streamed for each month, in order by month.

-----uniquie id - user_name

----time mins- convert into hours

---calcualuate total hours for by month

- display the results

SELECT

    DATEPART('yyyy-mm',time_minute) as Stream_year_month,

    FLOOR(SUM((count(time_minute)))/60) as total_hours

FROM minute_streamed

GROUP BY 1

ORDER BY 1

--2: Write a query that returns a row for each streamer with columns for their total hours streamed (in any category) and percentage of hours streamed in a Call of Duty game category. Examples of Call of Duty games include:

/*

Call of Duty: Black Ops

Call of Duty: Modern Warfare

Call of Duty: Advanced Warfare

*/

----- 

streamer_name | total_hrs | %hours COD / Total_hrs )0.00

WITH hrs AS

(

  SELECT distinct streamer,

         category

        SUM( count(time_minute))/60 as total_hrs

  FROM table1

  GROUP BY 1,2

)

| x | fortnite| 1

SELECT streamer_name

      SUM(Total_hrs) as total_time

        ROUND(SUM(CASe WHEN category like 'Call of Duty%' THEN total_hrs END)*1.0/ SUM(total_hrs),2) as COD_%

FROM hrs

GROUP BY streameR_name

ORDER BY total_time DESC

--3: Write a query that returns a row for each streamer with columns for their average concurrent viewers in 2019 and their total minutes viewed from viewers in Japan (JP) in 2019.

2019

streamer_name | Avg (con_viewes)/ streamer | total_mins for JP users

SELECT A.Streamer_name , A.total_jp_mins , B. con_viewers

 WITH x as

(SELECT streamer,

       COUNT(time_minute) as total_minutes

FROM minutes_viewed

WHERE YEAR(time_minute) = 2019 and viewer_countr = 'JP'

GROUP BY streamer

) A

 Y AS  

  (SELECT m.streamer_name,

       AVG (con_viewers)

FROM mins_streamed m

WHERE year(m.time_minute) = 2019

GROUP BY m.streamer_name

) B

SELECT X.Streamer_name , X.total_jp_mins , Y. con_viewers

FROM X

LEFT  JOIN Y

ON X.Streamer_name = Y.streamer

Order by 1

--4: Let's say we consider someone a New Twitch Viewer on the first day they watch Twitch. For each day and each streamer, output each streamer’s percentage of New Twitch Viewers among all of their viewers.

day | streamer| (new users/ total users)%

WITH X AS

(

SELECT 

    LEFT(time_minute,10) as day,

    streamer_name,

    ISNULL(count(*),0) as total_viewers

FROM min_viewed

GROUP BY 1,2

 )   

    date| x | 120

SELECT 

 X.Day, X.streamer_name

  ROUND(( SUM((CASE WHEN x.day = Y.first_log in THEN 1 else 0 end))*1.0 / X.Total_viewers ),2) as New_%

FROM X ,

(SELECT

     viewer,

     MIN(LEFT(time_minute,10) as day)) as first_log_in

FROM min_viewed    

    GROUP BY viewer) Y

GROUP BY  X.Day, X.streamer_name

ORDER BY 1,2