Recent Price Changes

Custom SQL query returning 5 rows (hide)

WITH recent_prices AS (SELECT item_id, store_id, base_price, delivery_price, effective_date, LAG(base_price / 100.0) OVER (PARTITION BY item_id, store_id ORDER BY effective_date) AS previous_base_price, LAG(delivery_price / 100.0)  OVER (PARTITION BY item_id, store_id ORDER BY effective_date) AS previous_delivery_price, LAG(effective_date) OVER (PARTITION BY item_id, store_id ORDER BY effective_date) AS previous_date FROM item_prices WHERE effective_date >= date('now', '-30 days')) SELECT m.name, s.store_name, rp.previous_base_price AS old_base_price, rp.base_price / 100.0 AS new_base_price, rp.previous_delivery_price AS old_delivery_price, rp.delivery_price / 100.0 AS new_delivery_price, rp.previous_date AS old_date, rp.effective_date AS new_date FROM recent_prices rp JOIN menu_items m ON rp.item_id = m.id JOIN stores s ON rp.store_id = s.id WHERE rp.previous_base_price IS NOT NULL ORDER BY rp.effective_date DESC LIMIT 20

Edit SQL