Twitch Product Analyst | October 2020

?
AnonymousApril 5, 2021, 01:12 AM
Upvote
1
Downvote

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

Twitch
Product Analyst
I
Ichimoku
Top comment
Lorem ipsum dolor sit amet, consectetur adipiscing elit
sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.
F
Fibonacci
Sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et

quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est
I
Ichimoku
Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium doloremque laudantium 😀
F
Fibonacci
Sit voluptatem accusantium doloremque laudantium, totam rem aperiam, eaque ipsa quae ab illo inventore veritatis et

quasi architecto beatae vitae dicta sunt explicabo. Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est

Interview Guide

View the full Twitch Product Analyst interview guide

Related posts

Stripe Mid-Level Business Intelligence Engineer | November 2022
Amazon Entry Level Business Analyst | April 2022
Amazon Manager Business Intelligence Manager | June 2021
UBS Senior Quantitative Analyst | April 2021
There's so much more to Interview Query! Sign up to access hundreds of interview questions, expert coaching and a flourishing data science community.