How would you use SQL window functions to calculate rolling averages, rank markets by daily price, and compare each day to the previous day?
Use the provided markets and market_prices tables. Calculate results for each market and recorded price date, including a three-row rolling average and the previous recorded price.
market_name, price_date, daily_price, rolling_3_day_avg, daily_price_rank, previous_day_price, and change_from_previous_day.price_date, daily_price_rank, and market_id.| Column | Type | Description |
|---|---|---|
| market_idPK | INT | Unique market identifier |
| market_name | VARCHAR(100) | Market display name |
| region | VARCHAR(50) | Geographic region of the market |
| Column | Type | Description |
|---|---|---|
| price_idPK | INT | Unique price observation identifier |
| market_id | INT | Referenced market |
| price_date | DATE | Date of the price observation |
| price | DECIMAL(10,2) | Observed market price |