twitch_sql_analysis.sql

-- viewing data table stream
SELECT *
FROM stream
LIMIT 20;

-- viewing data table chat
SELECT *
FROM chat
LIMIT 20;

-- finding all unique games in stream table
SELECT DISTINCT game
FROM stream;

-- finding all unique channels in stream table
SELECT DISTINCT channel
FROM stream;

-- finding game with most views
SELECT game, COUNT(*) as views
FROM stream
GROUP BY game
ORDER BY COUNT(*) DESC;

-- identifying League of Legend views by country
SELECT country, COUNT(*) as 'LOL viewers'
FROM stream
WHERE game = 'League of Legends'
GROUP BY country
ORDER BY COUNT(*) DESC;

-- identifying stream player/device with the most views
SELECT player, COUNT(*)
FROM stream
GROUP BY player
ORDER BY COUNT(*) DESC;

-- example of applying genres to certain games
SELECT game,
 CASE
  WHEN game = 'Dota 2'
      THEN 'MOBA'
  WHEN game = 'League of Legends' 
      THEN 'MOBA'
  WHEN game = 'Heroes of the Storm'
      THEN 'MOBA'
    WHEN game = 'Counter-Strike: Global Offensive'
      THEN 'FPS'
    WHEN game = 'DayZ'
      THEN 'Survival'
    WHEN game = 'ARK: Survival Evolved'
      THEN 'Survival'
  ELSE 'Other'
  END AS 'genre',
  COUNT(*)
FROM stream
GROUP BY 1
ORDER BY 3 DESC;

-- viewing time data from stream table
SELECT time
FROM stream
LIMIT 10;



-- best time to stream in the US
SELECT strftime('%H', time),
   COUNT(*)
FROM stream
WHERE country = 'US'
GROUP BY 1
order by 2 desc;

-- best local time to stream based on views globally

SELECT strftime('%H', time), channel, game, COUNT(*) as views
FROM stream
GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 20;


-- combining chat and stream tables

WITH combined as
(
  SELECT *
FROM stream
JOIN chat
  ON stream.device_id = chat.device_id
)

SELECT *
from combined
limit 20;

-- utilizing window functions to gain insight on the number of views per player(device) by game and by channel(streamer)

SELECT 
    distinct channel,
    game,
    player,
    count(device_id) OVER (
      PARTITION BY player
      order by game
    ) as 'count_views'
FROM
    stream
    where subscriber = 'true'
  --  limit 20;