Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zaza' FROM DUAL UNION ALL
SELECT 5, 'Jerry', 'Torchiano' FROM DUAL;
5 rows affected

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
3 rows affected

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);
create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);
ALTER TABLE purchases 
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);
ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);
insert  into purchases (customer_id, product_id, quantity, purchase_date) 
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
          connect by level <= 23
union all
select 3, 100,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
          connect by level <= 60;
161 rows affected

-- original
WITH t as (
     select distinct CUSTOMER_ID,
    trunc(PURCHASE_DATE) dat
       from purchases
    )
    ,tt as (
        select t.*
        ,row_number() over (partition by CUSTOMER_ID order by dat) rn
        from t
    )
   ,ttt as (
   select CUSTOMER_ID, 
         min(dat) start_date,
         max(dat) end_date,
         count(*) day_count
     from tt
     group by 
      CUSTOMER_ID, dat-rn
      having count(*) >= 10
   )
   select 
           c.customer_id,
           c.first_name,
           c.last_name,  
           ttt.start_date,
           ttt.end_date,
           ttt.day_count
     from   customers c, ttt
    where  
    c.customer_id = ttt.customer_id;


CUSTOMER_ID
FIRST_NAME
LAST_NAME
START_DATE
END_DATE
DAY_COUNT


2
Lisa
Saladino
14-APR-2023 00:00:00
24-APR-2023 00:00:00
11

3
Micheal
Palmice
02-MAR-2022 00:00:00
16-MAR-2022 00:00:00
15

3
Micheal
Palmice
22-APR-2023 00:00:00
14-MAY-2023 00:00:00
23

4
Joseph
Zaza
01-JAN-2023 00:00:00
13-JAN-2023 00:00:00
13


-- with count
WITH t as (
     select CUSTOMER_ID,
    trunc(PURCHASE_DATE) dat,
    count(*) cnt
       from purchases
   group by CUSTOMER_ID, trunc(PURCHASE_DATE)
    )
    ,tt as (
        select t.*
        ,row_number() over (partition by CUSTOMER_ID order by dat) rn
        from t
    )
   ,ttt as (
   select CUSTOMER_ID, 
         min(dat) start_date,
         max(dat) end_date,
         count(*) day_count,
         sum(cnt) purchase_count
     from tt
     group by 
      CUSTOMER_ID, dat-rn
      having count(*) >= 10
   )
   select 
           c.customer_id,
           c.first_name,
           c.last_name,  
           ttt.start_date,
           ttt.end_date,
           ttt.day_count,
           ttt.purchase_count
     from   customers c, ttt
    where  
    c.customer_id = ttt.customer_id;


CUSTOMER_ID
FIRST_NAME
LAST_NAME
START_DATE
END_DATE
DAY_COUNT
PURCHASE_COUNT


2
Lisa
Saladino
14-APR-2023 00:00:00
24-APR-2023 00:00:00
11
11

3
Micheal
Palmice
02-MAR-2022 00:00:00
16-MAR-2022 00:00:00
15
15

3
Micheal
Palmice
22-APR-2023 00:00:00
14-MAY-2023 00:00:00
23
23

4
Joseph
Zaza
01-JAN-2023 00:00:00
13-JAN-2023 00:00:00
13
60
CUSTOMER_ID	FIRST_NAME	LAST_NAME	START_DATE	END_DATE	DAY_COUNT
2	Lisa	Saladino	14-APR-2023 00:00:00	24-APR-2023 00:00:00	11
3	Micheal	Palmice	02-MAR-2022 00:00:00	16-MAR-2022 00:00:00	15
3	Micheal	Palmice	22-APR-2023 00:00:00	14-MAY-2023 00:00:00	23
4	Joseph	Zaza	01-JAN-2023 00:00:00	13-JAN-2023 00:00:00	13