#standardSQL

# Using the new "screen_view" event being tracked by Google Analytics for Firebase, can we figure out what the most
# common triplets of "screen progressions" are through our app? 

SELECT
  screen_0,
  screen_1,
  screen_2,
  COUNT(*) AS count
FROM (
  SELECT
    user_pseudo_id,
    event_timestamp,
    param.value.string_value AS screen_0,
    LEAD (param.value.string_value, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ) AS screen_1,
    LEAD (param.value.string_value, 2) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ) AS screen_2
  FROM
    `<dataset>.events_<date>`,
    UNNEST(event_params) AS param
  WHERE
    event_name = "screen_view"
    AND param.key = "firebase_screen_class"
  ORDER BY
    user_pseudo_id,
    event_timestamp )
WHERE
  screen_1 IS NOT NULL
  AND screen_2 IS NOT NULL
GROUP BY
  screen_0,
  screen_1,
  screen_2
ORDER BY
  count DESC