closed_funnel_bigQuery_firebase.sql

SELECT SUM(funnel_1) as f1_total, SUM(funnel_2) as f2_total FROM (
  SELECT
  if (event_name = "home_to_info1", 1, 0) AS funnel_1,
  if (event_name = "home_to_info1" AND next_event = "app_remove" AND next_timestamp - event_timestamp < 60 * 1000 * 1000, 1, 0) AS funnel_2
  FROM (
    SELECT event_name, user_pseudo_id , event_timestamp,
    LEAD(event_name, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_event,
    LEAD(event_timestamp, 1) OVER  (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS next_timestamp
    FROM `mobile-app-production-f37fd.analytics_189954145.events_*`
    WHERE event_name = "home_to_info1" OR event_name = "app_remove"
    AND _TABLE_SUFFIX BETWEEN '20180629' AND '20181228'
    ORDER BY 2,3
  )
)