Q2. Write a query to detect a sudden drop ( >30% ) in daily revenue for any restaurant.
CREATE TABLE zomato_restaurant_orders (
order_id NUMBER
, restaurant_id VARCHAR2(10)
, order_date DATE
, order_value NUMBER );
BEGIN
INSERT INTO zomato_restaurant_orders VALUES (1, 'R1', DATE '2024-07-01', 1000);
INSERT INTO zomato_restaurant_orders VALUES (2, 'R1', DATE '2024-07-01', 100);
INSERT INTO zomato_restaurant_orders VALUES (3, 'R1', DATE '2024-07-02', 950);
INSERT INTO zomato_restaurant_orders VALUES (4, 'R1', DATE '2024-07-03', 600);
INSERT INTO zomato_restaurant_orders VALUES (5, 'R1', DATE '2024-07-04', 300);
INSERT INTO zomato_restaurant_orders VALUES (6, 'R1', DATE '2024-07-04', 250);
INSERT INTO zomato_restaurant_orders VALUES (7, 'R1', DATE '2024-07-05', 500);
INSERT INTO zomato_restaurant_orders VALUES (8, 'R2', DATE '2024-07-01', 1500);
INSERT INTO zomato_restaurant_orders VALUES (9, 'R2', DATE '2024-07-02', 1400);
INSERT INTO zomato_restaurant_orders VALUES (10, 'R2', DATE '2024-07-03', 800);
INSERT INTO zomato_restaurant_orders VALUES (11, 'R2', DATE '2024-07-03', 1000);
END;
/How to achieve this?
1. Aggregate Daily Revenue per Restaurant
2. Use LAG() to Compare with Previous Day
3. Calculate Percentage Drop
4. Filter Only Significant Drops
1. Aggregate Daily Revenue per Restaurant
Group orders by restaurant_id and order_date and use SUM(order_value) to get total revenue per day
SELECT restaurant_id
, order_date
, SUM(order_value) as revenue
FROM zomato_restaurant_orders
GROUP BY order_date, restaurant_id
ORDER BY restaurant_id, order_date ;
2. Use LAG() to Compare with Previous Day
SELECT restaurant_id
,order_date
,revenue
,LAG(revenue)OVER(PARTITION BY restaurant_id ORDER BY order_date) as prev_day_revenue
FROM (SELECT restaurant_id
, order_date
, SUM(order_value) as revenue
FROM zomato_restaurant_orders
GROUP BY order_date, restaurant_id
ORDER BY restaurant_id, order_date) ;
3. Calculate Percentage Drop
SELECT restaurant_id
, order_date
, Revenue
, prev_day_revenue
, Round((revenue - prev_day_revenue) / prev_day_revenue * 100, 2) as Drop_Percentage
FROM ( SELECT restaurant_id
, order_date
, revenue
, LAG(revenue)OVER(PARTITION BY restaurant_id ORDER BY order_date) as prev_day_revenue
FROM (SELECT restaurant_id
, order_date
, SUM(order_value) as revenue
FROM zomato_restaurant_orders
GROUP BY order_date, restaurant_id
ORDER BY restaurant_id, order_date));
4. Finally, filter Only Significant Drops
SELECT restaurant_id
, order_date
, Revenue
, prev_day_revenue
, Round((revenue - prev_day_revenue) / prev_day_revenue * 100, 2) as Drop_Percentage
FROM ( SELECT restaurant_id
, order_date
, revenue
, LAG(revenue)OVER(PARTITION BY restaurant_id ORDER BY order_date) as prev_day_revenue
FROM (SELECT restaurant_id
, order_date
, SUM(order_value) as revenue
FROM zomato_restaurant_orders
GROUP BY order_date, restaurant_id
ORDER BY restaurant_id, order_date))
WHERE prev_day_revenue IS NOT NULL
AND (revenue < prev_day_revenue * 0.7) ;
Add a Comment