I was hoping someone could help me out. I need to find customers who have 10 or more purchases on consecutive days. Based on my sample data below only 1 row should satisfy this condition and that's for customer_id = 3 from 2023-03-12 to 2023-04-03
Below is my attempt, which I know isn't correct. Thanks in advance to all who respond.
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'John', 'Henry' FROM DUAL UNION ALL
SELECT 3, 'Lisa', 'Saladino' FROM DUAL;
CREATE TABLE purchases(
ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
CUSTOMER_ID NUMBER,
PURCHASE_DATE TIMESTAMP
);
INSERT INTO purchases
(CUSTOMER_ID, PURCHASE_DATE)
SELECT 1, TIMESTAMP '2023-04-03 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual CONNECT BY LEVEL <= 6 UNION ALL
SELECT 2, TIMESTAMP '2023-04-03 01:41:25' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM dual CONNECT BY LEVEL <= 7 UNION ALL
SELECT 3, TIMESTAMP '2023-04-23 21:31:25' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM dual CONNECT BY LEVEL <= 7 UNION ALL
SELECT 3, TIMESTAMP '2023-03-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM dual
CONNECT BY LEVEL <=20;
select purchases.*
from (select purchases.*, count(*) over (partition by customer_id) as cnt
from (select purchases.*,
(row_number() over (partition by customer_id order by purchase_date) -
row_number() over (partition by customer_id order by purchase_date)
) as grp
from purchases
) purchases
) purchases
where cnt >= 10;