Given a table of inventory transactions, write a query to calculate the rolling 7-day average of raw material consumption at ATC Manufacturing.
Use transaction dates with non-null dates. Treat dates without qualifying raw material consumption as zero when they appear in the transaction data. The rolling average includes the current date and the preceding six calendar days.
transaction_date, daily_consumption, and rolling_7_day_avg.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique inventory transaction identifier |
| transaction_date | DATE | Date on which the inventory transaction occurred |
| material_category | VARCHAR(30) | Inventory category, such as raw material or finished goods |
| transaction_type | VARCHAR(30) | Type of inventory movement |
| quantity | NUMERIC(12,2) | Quantity involved in the transaction |