QuestionQ93

VerifiedData Analysis and Presentation

Your company operates several retail locations and records the total number of sales at each location every day. You want to use SQL to calculate the weekly moving average of sales by location to identify trends for every store. Which query should you use?

Explanation

In standard SQL window functions, AVG(total_sales) OVER (PARTITION BY store_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) computes, for every row, the average of that day's sales together with the six preceding calendar days within the same store, which is exactly a weekly (7-row) moving average calculated independently for every location; partitioning or ordering by any column other than store_id and date, respectively, would mix data across stores or across the wrong ordering.

Community Discussion

No comments yet. Be the first to start the discussion!