Skip to content

Instantly share code, notes, and snippets.

@ivanpk

ivanpk/article6.sql Secret

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