#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 encountering the start event within a certain time window (4 
# hours, in this example.)                                                                                                         

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 user_pseudo_id ORDER BY event_timestamp) AS funnel_end_time
  FROM (
    SELECT
      event_name,
      IF (event_name = "start_event",
        event_timestamp,
        NULL) AS funnel_start_time,
      IF (event_name = "end_event",
        event_timestamp,
        NULL) AS funnel_end_time,
      user_pseudo_id,
      event_timestamp
    FROM
      #  `<dataset>.events_<date>`
    WHERE
      event_name = "start_event"
      OR event_name = "end_event"
      AND _TABLE_SUFFIX BETWEEN '<yyyymmdd>'
      AND '<yyyymmdd>'
    ORDER BY
      user_pseudo_id,
      event_timestamp) )