Associate Data Practitioner · Free Practice Question Medium
Question 20
A marketing analyst needs to calculate a 7-day rolling average of daily sales using BigQuery. Which SQL feature is most appropriate?
-
A
Common Table Expressions (CTEs)
-
B
Window functions
-
C
UNNEST function
-
D
GROUP BY clause
Reveal correct answer
Correct answer: B
Explanation
Option B is CORRECT. Window functions in BigQuery SQL are specifically designed for calculating values across a set of rows related to the current row, making them perfect for computing rolling averages. With window functions, you can define a "window" of rows (in this case, 7 days) and perform aggregate calculations over that window for each row. The syntax would use the AVG() function with an OVER clause that defines the window frame, such as: AVG(daily_sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
Option A is INCORRECT. Common Table Expressions (CTEs) help organize complex queries but don't directly provide rolling calculation functionality. They're useful for query readability and breaking down complex logic into manageable pieces, but you'd still need window functions within the CTE to calculate rolling averages.
Option C is INCORRECT. The UNNEST function is used for working with arrays, not for time-based calculations. It expands arrays into rows but doesn't provide functionality for calculating values across multiple rows like rolling averages.
Option D is INCORRECT. The GROUP BY clause aggregates data into groups but doesn't maintain the row-by-row context needed for rolling calculations. It collapses rows into summary values rather than maintaining the sequential relationship required for rolling averages.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
