#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_dim.app_info.app_instance_id AS app_instance,
    event.timestamp_micros AS event_timestamp,
    param.value.string_value AS screen_0,
    LEAD (param.value.string_value, 1) OVER (PARTITION BY user_dim.app_info.app_instance_id ORDER BY event.timestamp_micros) AS screen_1,
    LEAD (param.value.string_value, 2) OVER (PARTITION BY user_dim.app_info.app_instance_id ORDER BY event.timestamp_micros) AS screen_2
  FROM
    `<dataset>.app_events_<date>`,
    UNNEST(event_dim) AS event,
    UNNEST(event.params) AS param
  WHERE
    event.name = "screen_view"
    AND param.key = "firebase_screen_class"
  ORDER BY
    app_instance,
    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