Skip to content

Instantly share code, notes, and snippets.

@ToddKerpelman

ToddKerpelman/cohort3.sql

Created May 16, 2019 20:25
Show Gist options
  • Save ToddKerpelman/7ee45b1414c47103de7ee718d238247c to your computer and use it in GitHub Desktop.
Save ToddKerpelman/7ee45b1414c47103de7ee718d238247c to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cohort3.sql
SELECT DISTINCT user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00"))
AND event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-15 00:00:00", "-7:00"))
AND _TABLE_SUFFIX BETWEEN '20180807' AND '20180815'
AND user_pseudo_id IN (
SELECT DISTINCT user_pseudo_id
FROM `firebase-public-project.analytics_153293282.events_*`
WHERE event_name = 'first_open'
AND event_timestamp >= UNIX_MICROS(TIMESTAMP("2018-08-01 00:00:00", "-7:00"))
AND event_timestamp < UNIX_MICROS(TIMESTAMP("2018-08-08 00:00:00", "-7:00"))
AND _TABLE_SUFFIX BETWEEN '20180731' AND '20180808'
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment