SQL — Detecting Unusual Product Price Changes

To monitor pricing trends, a company wants to identify products that have had an unusually large price increase. A row qualifies when current_price > 1.5 × average_price (i.e. the current price is more than 50% above the historical average).

Output columns: product_id | name | current_price | average_price.

• current_price = the latest price row in price_history (the one with the largest changed_at).
• average_price = average price across the entire dataset for that product, rounded to two decimal places.
• Sort by average_price DESC, then by product_id ASC.

Schema:

products(id, name, brand) — id PK. price_history(id, product_id, price, changed_at) — id PK, product_id FK referencing products.id, price DECIMAL(10,2), changed_at DATE.

Sample data:

products:

id	name	brand
101	Mate Pro X	Huawei
102	iPhone 11	Apple

price_history:

id	product_id	price	changed_at
201	102	1000.30	2023-09-01
202	101	1020.10	2023-09-15
203	101	3500.00	2023-09-20

Expected output:

product_id	name	current_price	average_price
101	Mate Pro X	3500.00	2260.05

Reasoning: iPhone 11 has only one row (current_price equals average_price), so it fails the > 1.5 × avg threshold. Mate Pro X averages (1020.10 + 3500.00) / 2 = 2260.05, and 3500.00 > 1.5 × 2260.05 = 3390.075, so it qualifies.