Write a SQL query to calculate the moving average of a commodity's price over a rolling 7-day window, partitioned by the market exchange at Castleton Commodities International.
Use the commodity_prices and market_exchanges tables. Include prices recorded within the preceding six calendar days and the current date, while ignoring NULL prices.
market_exchange, commodity, price_date, price, and moving_average_7d.| Column | Type | Description |
|---|---|---|
| exchange_idPK | INT | Unique exchange identifier |
| exchange_code | VARCHAR(20) | Exchange code used in reporting |
| exchange_name | VARCHAR(100) | Full exchange name |
| Column | Type | Description |
|---|---|---|
| price_idPK | INT | Unique price record identifier |
| exchange_id | INT | Referenced market exchange |
| commodity | VARCHAR(50) | Commodity being priced |
| price_date | DATE | Date of the observed price |
| price | NUMERIC(12,4) | Commodity price for the date |