Skip to content

Instantly share code, notes, and snippets.

@cgi-ace
Created June 16, 2021 06:56
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save cgi-ace/4c5c15f41c5dfe4d44da618a58a8554e to your computer and use it in GitHub Desktop.
--------------- Retention--- A sample
with Week_0 AS (
select distinct user_pseudo_id
from `firebase-public-project.analytics_153293282.events_*`
WHERE event_name = 'first_open'
AND _TABLE_SUFFIX Between '20180820' AND '20180826'
),
week_1 AS (
select distinct user_pseudo_id
from `firebase-public-project.analytics_153293282.events_*`
WHERE event_name = 'user_engagement'
AND _TABLE_SUFFIX Between '20180827' AND '20180902'
),
week_2 AS (
select distinct user_pseudo_id
from `firebase-public-project.analytics_153293282.events_*`
WHERE event_name = 'user_engagement'
AND _TABLE_SUFFIX Between '20180903' AND '20180909'
)
select
(select count(*) from Week_0 ) as w0_cohort,
(select count(*) from week_1 Join Week_0 using (user_pseudo_id)) as w1_cohort ,
(select count(*) from week_2 Join Week_0 using (user_pseudo_id)) as w2_cohort
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment