article4.sql

WITH
-- build the arrays of distinct user ids per day
daily AS (
    SELECT
        DATE(created_at) day,
        STRING_AGG(DISTINCT user_id) users
    FROM
        `example-project.ivan.article_example_dataset`
    GROUP BY
        1
),
-- build an array of the 90 day window of users arrays (will be massive)
ninety_day_window AS (
    SELECT
        day,
        STRING_AGG(users) OVER (ORDER BY UNIX_DATE (day)
        RANGE BETWEEN 89 PRECEDING AND CURRENT ROW) users
FROM daily
)
SELECT
    day,
    (
        SELECT
            APPROX_COUNT_DISTINCT(id)
        FROM
            UNNEST(SPLIT(users)) AS id) unique90days
FROM
    ninety_day_window
ORDER BY
    1 DESC;