Price Volatility

Identifies items with the most volatile prices based on frequency of changes and price range

Custom SQL query returning 20 rows (hide) 

WITH price_stats AS (SELECT item_id, store_id, COUNT(*) AS price_change_count, MAX(base_price) - MIN(base_price) AS price_range FROM item_prices GROUP BY item_id, store_id) SELECT m.name, s.store_name, ps.price_change_count, ps.price_range, (ps.price_range * ps.price_change_count) AS volatility_score FROM price_stats ps JOIN menu_items m ON ps.item_id = m.id JOIN stores s ON ps.store_id = s.id ORDER BY volatility_score DESC LIMIT 20

Edit SQL

This data as json, CSV

namestore_nameprice_change_countprice_rangevolatility_score
Coffee Traveller ION Orchard 3 240 720
Cookie Crumble Cheesecake ION Orchard 3 40 120
Good Ol' NY Cheesecake ION Orchard 3 40 120
Signature Chocolate Cake ION Orchard 3 40 120
Strawberry Shortcake ION Orchard 3 40 120
Hojicha Tea Cream Frappuccino® ION Orchard 2 60 120
Caramel Frappuccino® ION Orchard 2 50 100
Mocha Frappuccino® ION Orchard 2 50 100
Espresso Frappuccino® ION Orchard 2 50 100
White Chocolate Mocha Frappuccino® ION Orchard 2 50 100
Vanilla Cream Frappuccino® ION Orchard 2 50 100
Mango Passion Fruit & Hibiscus ION Orchard 2 50 100
Raspberry Black Currant & Oolong ION Orchard 2 50 100
Dark Mocha Frappuccino® ION Orchard 2 40 80
Java Chip Frappuccino® ION Orchard 2 40 80
Caramel Macchiato Frappuccino® ION Orchard 2 40 80
Chocolate Chip Cream Frappuccino® ION Orchard 2 40 80
Caffè Mocha ION Orchard 2 40 80
Caramel Macchiato ION Orchard 2 40 80
Vanilla Latte ION Orchard 2 40 80