BigQuery analytic functions compute values over a group of rows and returns a single result for each row, making it a powerful tool for feature engineering, especially on time series data. However, analytic functions are not always a good solution, and rolling count distinct is an example.

I recently got a question of calculating a rolling count of how many users logged in during the past 30 and 365 days period. The first solution came to my mind was Analytic function in Standard SQL to perform a Count Distinct over partition by date. However, it proved not to be a…

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store