Q3. For each user, calculate their order streak — consecutive days with at least one order.
CREATE TABLE zomato_orders ( order_id NUMBER PRIMARY KEY
, user_id NUMBER
, order_value NUMBER
, ORDER_DATE DATE );
BEGIN
INSERT INTO ZOMATO_ORDERS VALUES (1, 101, 200, TO_DATE('2025-04-15', 'YYYY-MM-DD'));
INSERT INTO zomato_orders VALUES (2, 101, 220, TO_DATE('2025-04-16', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (3, 101, 240, TO_DATE('2025-04-17', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (4, 101, 110, TO_DATE('2025-04-17', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (5, 101, 500, TO_DATE('2025-04-20', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (6, 101, 300, TO_DATE('2025-04-21', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (7, 101, 600, TO_DATE('2025-04-27', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (8, 102, 300, TO_DATE('2025-04-10', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (9, 102, 180, TO_DATE('2025-04-10', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (10, 102, 670, TO_DATE('2025-04-10', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (11, 102, 400, TO_DATE('2025-04-11', 'YYYY-MM-DD'));
INSERT INTO ZOMATO_ORDERS VALUES (12, 102, 350, TO_DATE('2025-04-12', 'YYYY-MM-DD'));
INSERT INTO zomato_orders VALUES (13, 102, 220, TO_DATE('2025-04-13', 'YYYY-MM-DD'));
END;
/Goal: Find all streaks of consecutive days where they placed at least one order per day, and count the length of each streak (continious days).
SELECT user_id
, MIN(order_date) AS streak_start
, MAX(order_date) AS streak_end
, COUNT(*) AS streak_length
FROM ( SELECT user_id
, order_date
, order_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS streak_anchor
FROM ( SELECT DISTINCT user_id, order_date
FROM ZOMATO_ORDERS )
)
GROUP BY user_id, streak_anchor
ORDER BY user_id, streak_start;
Add a Comment