Retention from Firebase data in BigQuery via HyperLogLog++

WITH FIRST_ACTION AS (

SELECT
        TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY) as DATE,
        HLL_COUNT.INIT(user_id) as USER_ID_SET
    FROM `<dataset>.<project_id>.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20200201' AND '20200221' AND event_name = 'session_start'
    GROUP BY 1
    
), RETURNING_ACTION AS (

    SELECT
        TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY) as DATE,
        HLL_COUNT.INIT(user_id) as USER_ID_SET
    FROM `<dataset>.<project_id>.events_*`
    WHERE _TABLE_SUFFIX BETWEEN '20200201' AND '20200221' AND event_name = 'session_start'
    GROUP BY 1
    
), DET AS (

    SELECT
        FA.DATE,
        TIMESTAMP_DIFF(RA.DATE, FA.DATE, DAY) AS DIFF,
        HLL_COUNT.EXTRACT(FA.USER_ID_SET) AS FA_USER_COUNT,
        HLL_COUNT.EXTRACT(RA.USER_ID_SET) AS RA_USER_COUNT,
        ARRAY[FA.USER_ID_SET, RA.USER_ID_SET] AS USER_ID_ARRAY
    FROM FIRST_ACTION FA
    JOIN RETURNING_ACTION RA
    ON (FA.DATE <= RA.DATE)
    
), AGG AS (

    SELECT
        DET.DATE,
        DET.DIFF,
        FA_USER_COUNT,
        RA_USER_COUNT,
        V as VALUE
    FROM DET, UNNEST(DET.USER_ID_ARRAY) V
    
)

  SELECT
    date(DATE) as date,
    DIFF as day,
    (FA_USER_COUNT + RA_USER_COUNT) - HLL_COUNT.EXTRACT(SIM) as users
  FROM (
    SELECT
        AGG.DATE,
        AGG.DIFF,
        FA_USER_COUNT,
        RA_USER_COUNT,
        HLL_COUNT.MERGE_PARTIAL(VALUE) AS SIM
    FROM AGG
    GROUP BY 1, 2, 3, 4)
    order by 1,2