Rolling 7-Day Sales Average

BASF wants to monitor daily sales trends for products tracked in its commercial sales system. Write a PostgreSQL query that calculates a rolling 7-day average of completed sales for each product.

Requirements

1. Aggregate completed transactions into one daily sales total per product, treating missing sales days as zero.
2. Generate a continuous calendar between each product's first and last completed sale.
3. Calculate the average across the current day and previous six calendar days using a window function.
4. Return product name, date, daily sales, and the rolling average, ordered by product and date.

Output details

• Return columns named product_name, sale_date, daily_sales, and rolling_7_day_avg, in that order.
• Order results by product_name ascending, then sale_date ascending.
• Round rolling_7_day_avg to two decimal places.
• Treat each product’s first and last completed-sale dates as inclusive calendar bounds.
• Treat a daily total with only NULL sale amounts as numeric zero.