#standardSQL

# A closed funnel with time constraints!
# Count the number of occurrences a user encountered a "start_event" event, and then the number of times
# they encountered an "end_event" event after pre

SELECT
  COUNTIF(funnel_start_time IS NOT NULL) AS funnel_begin_count,
  COUNTIF(funnel_end_time - funnel_start_time < 4 * 60 * 60 * 1000 * 1000) AS funnel_end_count
FROM (
  SELECT
    funnel_start_time,
    LEAD(funnel_end_time, 1) OVER (PARTITION BY app_instance_id ORDER BY event_time) AS funnel_end_time
  FROM (
    SELECT
      event.name,
      IF (event.name = "start_event",
        event.timestamp_micros,
        NULL) AS funnel_start_time,
      IF (event.name = "end_event",
        event.timestamp_micros,
        NULL) AS funnel_end_time,
      user_dim.app_info.app_instance_id,
      event.timestamp_micros AS event_time
    FROM
      `<dataset>.app_events_*`, 
      UNNEST(event_dim) AS event
    WHERE
      event.name = "start_event"
      OR event.name = "end_event"
      AND _TABLE_SUFFIX BETWEEN '<date1>'
      AND '<date2>'
    ORDER BY
      app_instance_id,
      event.timestamp_micros ) )