Pricing teams need an audit trail showing every time a product's price changed, by how much, and by what percentage. This helps identify aggressive price hikes, accidental decreases, and patterns around promotional periods. Using the product_prices table, return product_id, prev_price, new_price, price_change (new − prev), change_pct (percentage change rounded to 2 decimal places), and changed_at for every price change event, ordered by product_id then changed_at.
product_prices
| column | type |
|---|---|
| id | INTEGER |
| product_id | INTEGER |
| price | NUMERIC |
| changed_at | DATE |
product_prices
| id | product_id | price | changed_at |
|---|---|---|---|
| 1 | 1 | 10.00 | 2024-01-01 |
| 2 | 1 | 12.00 | 2024-02-01 |
| 3 | 1 | 11.00 | 2024-03-01 |
| 4 | 2 | 50.00 | 2024-01-15 |
| 5 | 2 | 60.00 | 2024-02-15 |
| product_id | prev_price | new_price | price_change | change_pct | changed_at |
|---|---|---|---|---|---|
| 1 | 10.00 | 12.00 | 2.00 | 20.00 | 2024-02-01 |
| 1 | 12.00 | 11.00 | -1.00 | -8.33 | 2024-03-01 |
| 2 | 50.00 | 60.00 | 10.00 | 20.00 | 2024-02-15 |
Product 1's initial entry (Jan 1 at $10.00) has no predecessor so it produces no output row. The Feb 1 change from $10 to $12 is a +$2.00 / +20.00% increase; the Mar 1 rollback from $12 to $11 is a -$1.00 / -8.33% decrease. Product 2 has one change event: $50 to $60, +20.00%.