An analyst in BigQuery needs a trailing seven-day average of daily revenue for each row, keeping one output row per day. Which expression fits?
Show answer and explanation
Answer: A
A window function with ROWS 6 PRECEDING uses the current row plus the six rows before it, which is seven days when each day has one row. Ordering by the day column keeps the frame chronological, and the aggregate returns one value per input row, so the daily grain survives. A GROUP BY would collapse the rows, and COUNT or MAX compute different statistics.
Why the other options are wrong
- B. Dividing a sum by seven with GROUP BY returns one row per group and assumes every day has revenue, so it does not produce a rolling average on each row. It also misstates days with missing data.
- C. COUNT tallies how many rows fall inside the window, so the result is a row count rather than revenue. It answers a different question than the average the analyst needs.
- D. MAX returns the largest revenue in the window, which describes a peak rather than an average. The scenario asks for a mean over the trailing week.