Dynamic Bracket-dependent Return User Retention in BigQuery Firebase Project

-- Source: https://t.me/BigQuery
-- Сalculation example bracket-dependent return retention (https://applift.com/blog/user-retention)
-- weekly (1 week = day2-8, 2 week = day9-15 ...)

WITH download AS (

SELECT 
user_pseudo_id, app_info.version,
EXTRACT (DATE FROM (SELECT TIMESTAMP_MICROS(event_timestamp))) AS day_0 
FROM `your_project.your_table.events_*` 
where _TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)) 
and FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
and event_name = 'first_open'
group by 1,2,3

), engagement AS (

SELECT 
user_pseudo_id, 
EXTRACT (DATE FROM (SELECT TIMESTAMP_MICROS(event_timestamp))) AS engage 
FROM `your_project.your_table.events_*` 
where _TABLE_SUFFIX between FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -360 DAY)) 
and FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
-- and event_name = 'engagement_name'
group by 1,2

), f as (

SELECT * from download
left join engagement using (user_pseudo_id)

)

SELECT version, CEIL(IEEE_DIVIDE(DATE_DIFF(engage, day_0, DAY),7)) as week, count(distinct user_pseudo_id) as users
FROM f
where DATE_DIFF(engage, day_0, DAY)>-1
group by 1,2