I’m working with a massive dataset of daily user logins. I need to calculate a 7-day rolling average for retention to spot trends, but my current self-join is incredibly slow. Is there a more efficient way to use window functions like AVG() OVER() to handle this without crashing my BigQuery instance?
3 answers
Using AVG() OVER() with a ROWS BETWEEN clause is the industry standard for this. For a 7-day average, use: AVG(login_count) OVER(ORDER BY login_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). This avoids the Cartesian product of a self-join and is significantly lighter on memory. I implemented this for a fintech client last year and reduced their processing time from 40 minutes to under 3 minutes. Make sure your date column is indexed, or even better, partitioned by month if you are using a cloud-native warehouse like BigQuery or Snowflake for better cost efficiency.
Are you accounting for gaps in dates where no users logged in, or does your dataset have a continuous row for every single calendar day?
Window functions are definitely the way to go. Just ensure you are using ORDER BY within the over clause, or the result will be non-deterministic and useless.
I totally agree with Michael. Without the proper ordering, the window frame doesn't know the temporal sequence, which is the most common mistake I see.
That’s a great point, David. If there are missing days, the ROWS BETWEEN logic will actually average the last 7 records, not the last 7 days. To fix this, I usually use a recursive CTE to generate a date backbone first and then left join the login data. Does that sound like too much overhead for a dataset with 100 million rows, or is there a more native SQL way to handle date gaps?