Skip to content

Instantly share code, notes, and snippets.

@phsantiago
Created December 18, 2018 22:47
Show Gist options
  • Save phsantiago/b04c55dfffbbd8c2c80f1f61d6263c09 to your computer and use it in GitHub Desktop.
Save phsantiago/b04c55dfffbbd8c2c80f1f61d6263c09 to your computer and use it in GitHub Desktop.
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
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment