7-Day Rolling Average Sales

Walmart Labs wants category-level sales trends for Walmart's digital commerce reporting. Write a PostgreSQL query that calculates a seven-day rolling average of daily sales for each product category.

The rolling window must cover the current date and the six preceding calendar dates. Dates with no sales must count as zero, and categories with products but no sales during the period must still appear.

Requirements

1. Aggregate sales to one row per category and calendar date.
2. Generate dates from 2024-01-01 through 2024-01-07 and fill missing sales with zero.
3. Use a window function partitioned by category to calculate the rolling average.
4. Return results ordered by category and sales date.

Output details

• Return columns in this order: category, sale_date, daily_sales, rolling_7_day_avg.
• Sort by category ascending, then sale_date ascending.
• Round rolling_7_day_avg to exactly two decimal places.
• Treat the date range endpoints 2024-01-01 and 2024-01-07 as inclusive.
• Represent missing or NULL daily sales as numeric 0.00.