Objective

Given a table sales with columns sale_date (in YYYY-MM-DD format) and amount, write an SQL query to compute the 7-day moving average of amount for each sale_date. The moving average should include the current day and the six preceding days. Round the moving average to two decimal places.

Additional information

• The output should include the columns sale_date, amount, and moving_average.
• Order the results by sale_date in ascending order.
• Utilize appropriate SQL window functions to efficiently calculate the moving average.
• Assume that there are no duplicate sale_date entries and that sale_date values are consecutive without gaps.

WITH daily_moving_avg AS (
    SELECT
        sale_date,
        amount,
        AVG(amount) OVER (
            ORDER BY sale_date
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
        ) AS moving_average
    FROM sales
)
SELECT
    sale_date,
    amount,
    ROUND(moving_average::numeric, 2) AS moving_average
FROM daily_moving_avg
ORDER BY sale_date;

Explanation

Step 1: Compute the 7-day moving average with a window function (CTE)

AVG(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) averages amount across a sliding window that covers the current row's date and the 6 calendar days before it, giving a 7-day rolling average.

AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS moving_average

Step 2: Round the moving average to two decimal places

In the outer query, ROUND(moving_average::numeric, 2) casts the result to numeric and rounds it to two decimal places for clean output.

ROUND(moving_average::numeric, 2) AS moving_average

Step 3: Order results chronologically

ORDER BY sale_date presents the data in date order so the trend is easy to follow.

ORDER BY sale_date;