Skip to content

Instantly share code, notes, and snippets.

@ivanpk

ivanpk/article4.sql Secret

Created Feb 5, 2020
Embed
What would you like to do?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment