Write a SQL query to compare current-day commodity prices against the prior day and flag assets with a drop greater than 5%.
Use the latest available price date as the current day. Compare only with an actual calendar prior day, and exclude assets without a valid prior-day price.
asset_id, asset_name, current_price, prior_price, and pct_changepct_change ascending, then asset_id ascending| Column | Type | Description |
|---|---|---|
| asset_idPK | INT | Unique identifier for a commodity asset |
| asset_name | VARCHAR(100) | Display name of the asset |
| commodity_type | VARCHAR(50) | Commodity classification |
| Column | Type | Description |
|---|---|---|
| price_idPK | INT | Unique price observation identifier |
| asset_id | INT | Referenced commodity asset |
| price_date | DATE | Date of the price observation |
| price | NUMERIC(12,4) | Commodity price for the asset and date |