Largest Price Increases

Custom SQL query returning 20 rows (hide)

WITH price_changes AS (SELECT p1.item_id, m.name, s.store_name, p1.base_price / 100.0 AS new_price, p2.base_price / 100.0 AS old_price, p1.effective_date AS new_date, p2.effective_date AS old_date, (p1.base_price - p2.base_price) / 100.0 AS price_difference FROM item_prices p1 JOIN item_prices p2 ON p1.item_id = p2.item_id AND p1.store_id = p2.store_id JOIN menu_items m ON p1.item_id = m.id JOIN stores s ON p1.store_id = s.id WHERE p1.effective_date > p2.effective_date AND p1.effective_date = (SELECT MAX(effective_date) FROM item_prices WHERE item_id = p1.item_id AND store_id = p1.store_id) AND p2.effective_date = (SELECT MAX(effective_date) FROM item_prices WHERE item_id = p1.item_id AND store_id = p1.store_id AND effective_date < p1.effective_date)) SELECT name, store_name, old_price, new_price, price_difference, old_date, new_date FROM price_changes ORDER BY price_difference DESC LIMIT 20

Edit SQL