Calculating MOVING AVERAGE using WINDOW function in SQL | BigQuery

[@eric-seastrand]

How could one adapt this solution to work with non-uniform time increments in the data? This simple 6 row approach works because there is exactly 1 row for each day. What if some days are missing? What if some days have multiple measurements? Examples: orders in an eCommerce system being placed at random times by customers. Or a baby’s milk consumption.

[@pivotalstats]

Hi, In that scenario, I think we can achieve this in 4 steps: 1. Aggregate the column with multiple measurements using SUM 2. Create a separate table with all dates in your date range 3. Join these two tables, so that in your final table no days are missing 4. Finally use the Window function on this final table Let me know if this helped, thanks!

[@adeyemiadenuga3798]

what if I don't want few rows I want all the rows must I specify rows with the key word ROWS BETWEEN 6 AND PRECEEDING CURRENT ROW

[@pivotalstats]

Hi, Can you elaborate what you meant by all rows? You will only use "Rows Between 6 Preceeding and Current Row" when you want 7 day moving average.