article6.sql

WITH daily AS (
    -- build the HLL sketch of distinct user ids per day
    SELECT
        DATE(created_at) day,
        HLL_COUNT.init(user_id) users_sketch
    FROM
        `example-project.ivan.article_example_dataset`
    GROUP BY
        1
),
-- build an array of the sketches of users
ninety_day_window AS (
    SELECT
        day,
        ARRAY_AGG(users_sketch) OVER (ORDER BY UNIX_DATE (day)
        RANGE BETWEEN 89 PRECEDING AND CURRENT ROW) rolling_sketch_array
FROM daily
)
SELECT
    day,
    (
        SELECT
            HLL_COUNT.merge(sketches)
        FROM
            UNNEST(rolling_sketch_array) sketches) rolling_sketch
FROM
    ninety_day_window
ORDER BY
    1 DESC;