media_analytics_start_stop_3.sql

WITH start_stop AS (
   SELECT title,
          user_id,
          play_session,
          play_session_sequence, 
          GENERATE_ARRAY(start_event.start, 
                         stop_event.stop
                        ) AS second_breakdown
     FROM start_event
     JOIN stop_event
          USING (title,
                 user_id,
                 length,
                 play_session,
                 play_session_sequence)
   
), session_sec_breakdown AS (
   SELECT title,
          user_id,
          length,
          play_session,
          ARRAY_CONCAT_AGG(second_breakdown) AS sec_breakdown
     FROM start_stop
 GROUP BY 1,2,3,4
   
), flat_play AS (
    SELECT title,
           user_id,
           sec_breakdown
      FROM (SELECT * EXCEPT (sec_breakdown),
                   (SELECT COUNT(DISTINCT x)
                      FROM UNNEST(sec_breakdown) x
                     WHERE x > 0
                   ) AS sec_played
           FROM session_sec_breakdown
           ) a
CROSS JOIN a.sec_played

), length_breakdown AS (
    SELECT title,
           GENERATE_ARRAY(0, length) AS length
      FROM start_event

), flatten_length AS (
    SELECT title,
           length
      FROM start_event
CROSS JOIN length_breakdown.length
)

   SELECT l.title,
          l.length,
          x.sec_played,
          COUNT(DISTINCT x.user_id) AS player
     FROM flatten_length l
LEFT JOIN (SELECT title, user_id, sec_breakdown
             FROM flat_play
          ) x
           ON l.title = x.title
          AND l.length = x.sec_breakdown