CREATE TABLE orders1 (
OrderID INT,
OrderDate DATE,
CustomerID INT
);
INSERT INTO orders1 (OrderID, OrderDate, CustomerID)
VALUES
(1, TO_DATE('2023-06-20','YYYY-MM-DD'), 1),
(2, TO_DATE('2023-06-21','YYYY-MM-DD'), 2),
(3, TO_DATE('2023-06-22','YYYY-MM-DD'), 3),
(4, TO_DATE('2023-06-21','YYYY-MM-DD'), 1),
(5, TO_DATE('2023-06-23','YYYY-MM-DD'), 3),
(6, TO_DATE('2023-06-22','YYYY-MM-DD'), 1),
(7, TO_DATE('2023-06-26','YYYY-MM-DD'), 4),
(8, TO_DATE('2023-06-27','YYYY-MM-DD'), 4),
(9, TO_DATE('2023-06-29','YYYY-MM-DD'), 4),
(10, TO_DATE('2023-06-29','YYYY-MM-DD'), 5),
(11, TO_DATE('2023-06-30','YYYY-MM-DD'), 5);
My effort:-
WITH cte AS (
SELECT
o.*, DENSE_RANK() over(ORDER BY orderdate) - DENSE_RANK() over (PARTITION BY customerid ORDER BY orderdate) as grp
FROM orders1 o
)
SELECT customerid, COUNT(DISTINCT orderdate) AS consecutive_days
FROM cte
WHERE grp > 0
GROUP BY customerid, grp
HAVING consecutive_days > 1;
o/p:-
I need output as below
Customer ID Consecutive days
1 2
3 2
5 2