Largest Price Increases

Custom SQL query returning 20 rows (hide)

WITH price_diff AS ( SELECT m.name AS item_name, s.store_name, p2.base_price / 100.0 AS old_price, p1.base_price / 100.0 AS new_price, (p1.base_price - p2.base_price) / 100.0 AS price_difference, p2.effective_date AS old_date, p1.effective_date AS new_date, ROW_NUMBER() OVER ( PARTITION BY m.name ORDER BY (p1.base_price - p2.base_price) DESC ) AS rank 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 item_name, store_name, old_price AS old_price_sgd, new_price AS new_price_sgd, price_difference AS absolute_price_increase_sgd, old_date AS previous_price_date, new_date AS current_price_date FROM price_diff WHERE rank = 1 ORDER BY absolute_price_increase_sgd DESC LIMIT 20;

Edit SQL