long ass sql

long.sql

WITH normalized_match(name, starttime, endtime, youtube_video, twitch_channel) AS
(VALUES
  ('Jan 24: Academy - C9 vs TL',   TIMESTAMP '2020-01-25 01:00', TIMESTAMP '2020-01-25 01:40', '999FdvjJyPY', 'Academy'),
  ('Jan 24: Academy - DIG vs EG',  TIMESTAMP '2020-01-25 01:00', TIMESTAMP '2020-01-25 01:40', '999FdvjJyPY', 'Academy'),
  ('Jan 24: Academy - TSM vs IMT', TIMESTAMP '2020-01-25 01:00', TIMESTAMP '2020-01-25 01:40', '999FdvjJyPY', 'Academy'),
  ('Jan 24: Academy - FLY vs CLG', TIMESTAMP '2020-01-25 01:00', TIMESTAMP '2020-01-25 01:40', '999FdvjJyPY', 'Academy'),
  ('Jan 24: Academy - 100T vs GG', TIMESTAMP '2020-01-25 01:49', TIMESTAMP '2020-01-25 02:20', '999FdvjJyPY', 'Academy'),
  ('Jan 25: LCS - C9 vs TL',       TIMESTAMP '2020-01-25 22:06', TIMESTAMP '2020-01-25 22:32', '3KABoEyHqUM', 'LCS'),
  ('Jan 25: LCS - CLG vs DIG',     TIMESTAMP '2020-01-25 22:58', TIMESTAMP '2020-01-25 23:38', '3KABoEyHqUM', 'LCS'),
  ('Jan 25: LCS - 100T vs GG',     TIMESTAMP '2020-01-26 00:02', TIMESTAMP '2020-01-26 00:44', '3KABoEyHqUM', 'LCS'),
  ('Jan 25: LCS - IMT vs FLY',     TIMESTAMP '2020-01-26 01:07', TIMESTAMP '2020-01-26 01:45', '3KABoEyHqUM', 'LCS'),
  ('Jan 25: Academy - CLG vs DIG', TIMESTAMP '2020-01-26 02:21', TIMESTAMP '2020-01-26 02:58', '3KABoEyHqUM', 'Academy'),
  ('Jan 26: LCS - DIG vs EG',      TIMESTAMP '2020-01-26 20:06', TIMESTAMP '2020-01-26 20:34', 'iKzZVyEK_WU', 'LCS'),
  ('Jan 26: LCS - TSM vs IMT',     TIMESTAMP '2020-01-26 20:59', TIMESTAMP '2020-01-26 22:00', 'iKzZVyEK_WU', 'LCS'),
  ('Jan 26: LCS - GG vs C9',       TIMESTAMP '2020-01-26 22:23', TIMESTAMP '2020-01-26 22:57', 'iKzZVyEK_WU', 'LCS'),
  ('Jan 26: LCS - FLY vs CLG',     TIMESTAMP '2020-01-26 23:22', TIMESTAMP '2020-01-26 23:53', 'iKzZVyEK_WU', 'LCS'),
  ('Jan 26: Academy - GG vs C9',   TIMESTAMP '2020-01-27 00:19', TIMESTAMP '2020-01-27 00:47', 'iKzZVyEK_WU', 'Academy'),
  ('Jan 27: Academy - IMT vs FLY', TIMESTAMP '2020-01-27 22:07', TIMESTAMP '2020-01-27 22:37', 'ISRhfEifPyc', 'Academy'),
  ('Jan 27: Academy - EG vs 100T', TIMESTAMP '2020-01-27 23:04', TIMESTAMP '2020-01-27 23:32', 'ISRhfEifPyc', 'Academy'),
  ('Jan 27: Academy - TL vs TSM',  TIMESTAMP '2020-01-28 00:07', TIMESTAMP '2020-01-28 00:51', 'ISRhfEifPyc', 'Academy'),
  ('Jan 27: LCS - EG vs 100T',     TIMESTAMP '2020-01-28 01:35', TIMESTAMP '2020-01-28 02:15', 'ISRhfEifPyc', 'LCS'),
  ('Jan 27: LCS - TL vs TSM',      TIMESTAMP '2020-01-28 02:40', TIMESTAMP '2020-01-28 03:19', 'ISRhfEifPyc', 'LCS')
), 
total_match(time) AS (
  SELECT SUM(DATE_DIFF('second', starttime, endtime)/60.0)
  FROM normalized_match
), 
league(name, time) AS (
  SELECT twitch_channel, SUM(DATE_DIFF('second', starttime, endtime)/60.0)
  FROM normalized_match
  GROUP BY 1
),
match(platform, name, starttime, endtime, videoid, league) AS (
  (SELECT 'YouTube', name, starttime, endtime, youtube_video, twitch_channel FROM normalized_match)
  UNION ALL
  (SELECT 'Twitch', name, starttime, endtime, twitch_channel, twitch_channel FROM normalized_match)
  UNION ALL
  (
    SELECT
      'LoL Esports',
      name, 
      starttime, 
      endtime, 
      'live/' || CASE WHEN LOWER(twitch_channel) = 'lcs' THEN 'lcs' ELSE 'lcs-academy' END || '/' || twitch_channel, 
      twitch_channel
    FROM normalized_match
  ) UNION ALL
  (
    SELECT
      'LoL Esports',
      name, 
      starttime, 
      endtime, 
      'live/' || CASE WHEN LOWER(twitch_channel) = 'lcs' THEN 'lcs' ELSE 'lcs-academy' END || '/' || youtube_video, 
      twitch_channel
    FROM normalized_match
  )
), 
twitch_session (broadcast_platform, deviceid, videoid, countrycode, starttime, endtime, platform) AS (
  SELECT 
    'Twitch', 
    deviceid, 
    videoid, 
    countrycode, 
    session_start, 
    session_end,
    platform
  FROM twitch_session
), 
youtube_session (broadcast_platform, deviceid, videoid, countrycode, starttime, endtime, platform) AS (
  SELECT 
    'YouTube', 
    deviceid, 
    videoid, 
    countrycode, 
    session_start_timestamp, 
    session_end_timestamp,
    platform
  FROM youtube_lcs_session
),
lolesports_session (broadcast_platform, deviceid, videoid, countrycode, starttime, endtime, platform) AS (
  SELECT 
    'LoL Esports', 
    deviceid, 
    videoid, 
    countrycode, 
    session_start, 
    session_end,
    platform
  FROM lolesports_session
),
session AS (
  (SELECT * FROM twitch_session)
  UNION ALL
  (SELECT * FROM youtube_session)
  UNION ALL
  (Select * FROM lolesports_session)
), 
session_during_match AS (
  SELECT match.name AS match_name,
         session.broadcast_platform,
         session.countrycode,
         deviceid,
         DATE_DIFF('second', GREATEST(match.starttime, session.starttime), LEAST(match.endtime, session.endtime)) AS duration,
         match.starttime AS match_start_time,
         match.endtime AS match_end_time,
         league,
         survey_factor.factor,
         country_weights.scale
  FROM match
  LEFT JOIN session 
    ON LOWER(session.videoid) = LOWER(match.videoid)
    AND session.starttime < match.endtime
    AND session.endtime >= match.starttime
    AND session.broadcast_platform = match.platform
  LEFT JOIN survey_factor
    ON session.countrycode = survey_factor.countrycode
    AND session.broadcast_platform = survey_factor.broadcaster
    AND DATE(session.starttime) = survey_factor.day
  LEFT JOIN country_weights_all_days country_weights
    ON session.countrycode = country_weights.country_code
    AND DATE(session.starttime) = country_weights.date
  WHERE DATE_DIFF('second', session.starttime, session.endtime) >= 60
    AND session.countrycode IN ('US', 'CA')
    AND session.platform = 'Desktop'
), 
match_metrics (row, total_hours, content_duration_minutes) AS (
  SELECT
    match_name,
    SUM(duration*scale/factor) / (60.0*60.0),
    ARBITRARY(DATE_DIFF('second', match_start_time, match_end_time)/60.0)
  FROM session_during_match
  GROUP BY match_name
), 
match_reach (row, total_reach) AS (
  SELECT
    match_name,
    SUM(scale/factor)
  FROM (
    SELECT DISTINCT match_name, deviceid, factor, scale
    FROM session_during_match
  )
  GROUP BY match_name
), 
country_metrics (row, total_hours, content_duration_minutes) AS (
  SELECT
    countrycode,
    SUM(duration*scale/factor) / (60.0*60.0),
    ARBITRARY(total_match.time)
  FROM session_during_match, total_match
  GROUP BY countrycode
), 
country_reach (row, total_reach) AS (
  SELECT
    countrycode,
    SUM(scale/factor)
  FROM (
    SELECT DISTINCT countrycode, deviceid, factor, scale
    FROM session_during_match
  )
  GROUP BY countrycode
), 
league_metrics (row, total_hours, content_duration_minutes) AS (
  SELECT
    league,
    SUM(duration*scale/factor) / (60.0*60.0),
    ARBITRARY(league.time)
  FROM session_during_match
  LEFT JOIN league
  ON league.name = session_during_match.league
  GROUP BY league
),
league_reach (row, total_reach) AS (
  SELECT
    league,
    SUM(scale/factor)
  FROM (
    SELECT DISTINCT league, deviceid, factor, scale
    FROM session_during_match
  )
  GROUP BY league
), 
platform_metrics (row, total_hours, content_duration_minutes) AS (
  SELECT
    broadcast_platform,
    SUM(duration*scale/factor) / (60.0*60.0),
    ARBITRARY(total_match.time)
  FROM session_during_match, total_match
  GROUP BY broadcast_platform
), 
platform_reach (row, total_reach) AS (
  SELECT
    broadcast_platform,
    SUM(scale/factor)
  FROM (
    SELECT DISTINCT broadcast_platform, deviceid, factor, scale
    FROM session_during_match
  )
  GROUP BY broadcast_platform
), 
total_metrics (row, total_hours, content_duration_minutes) AS (
  SELECT
    'Total',
    SUM(duration*scale/factor) / (60.0*60.0),
    ARBITRARY(total_match.time)
  FROM session_during_match, total_match
  GROUP BY 1
),
total_reach (row, total_reach) AS (
  SELECT
    'Total',
    SUM(scale/factor)
  FROM (
    SELECT DISTINCT deviceid, factor, scale
    FROM session_during_match
  )
)
SELECT
  row, 
  total_hours, 
  total_reach,
  total_hours*60 / content_duration_minutes AS ama
FROM
(
  (
    SELECT match_metrics.row, total_hours, total_reach, content_duration_minutes 
    FROM match_metrics 
    LEFT JOIN match_reach ON match_metrics.row = match_reach.row
  ) UNION ALL
  (
    SELECT country_metrics.row, total_hours, total_reach, content_duration_minutes 
    FROM country_metrics 
    LEFT JOIN country_reach ON country_metrics.row = country_reach.row
  ) UNION ALL
  (
    SELECT league_metrics.row, total_hours, total_reach, content_duration_minutes 
    FROM league_metrics 
    LEFT JOIN league_reach ON league_metrics.row = league_reach.row
  ) UNION ALL
  (
    SELECT platform_metrics.row, total_hours, total_reach, content_duration_minutes 
    FROM platform_metrics 
    LEFT JOIN platform_reach ON platform_metrics.row = platform_reach.row
  ) UNION ALL
  (
    SELECT total_metrics.row, total_hours, total_reach, content_duration_minutes 
    FROM total_metrics 
    LEFT JOIN total_reach ON total_metrics.row = total_reach.row
  )
)