Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'John', 'Doe' FROM DUAL UNION ALL
SELECT 2, 'Ann', 'Smith' FROM DUAL UNION ALL
SELECT 3, 'Mike', 'Jones' FROM DUAL;
3 rows affected

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
);
insert  into purchases (customer_id, product_id, quantity, purchase_date)
  select 1 AS id, 101, 1 AS q, DATE '2024-04-08' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1
  from dual connect by level <= 15
UNION all
  select 2, 102, 1, DATE '2024-03-08' + INTERVAL '14' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:00' DAY TO SECOND) * -1
   from dual connect by level <= 5
UNION ALL 
  select 3, 103, 1, DATE '2024-02-08' + INTERVAL '15' HOUR + ((LEVEL-1) * INTERVAL '0 23:59:59' DAY TO SECOND) * -1
   from dual connect by level <= 5;
25 rows affected

select customer_id,
            first_date,
            last_date,
          trunc(last_date) - trunc(first_date) + 1 as consecutive_days
from purchases 
match_recognize(
    partition by customer_id
    order by purchase_date
    measures
        first(purchase_date) as first_date,
        last(purchase_date) as last_date
    one row per match
    pattern(start_date P{9,})
    define P as purchase_date >= prev(trunc(purchase_date)) + interval '1' day
      and purchase_date < prev(trunc(purchase_date)) + interval '2' day
);


CUSTOMER_ID
FIRST_DATE
LAST_DATE
CONSECUTIVE_DAYS


1
2024-03-25 12:59:46.000000
2024-04-08 13:00:00.000000
15


select m.customer_id,
       m.first_name,
       m.last_name,
       m.first_date,
       m.last_date,
       trunc(m.last_date) - trunc(m.first_date) + 1 as consecutive_days
FROM   (
         SELECT p.*, c.first_name, c.last_name
         FROM   purchases p
                LEFT OUTER JOIN customers c
                ON c.customer_id = p.customer_id
       ) cp
       MATCH_RECOGNIZE(
         PARTITION BY customer_id
         ORDER BY     purchase_date
         MEASURES
           MIN(first_name)      AS first_name,
           MIN(last_name)       AS last_name,
           FIRST(purchase_date) AS first_date,
           LAST(purchase_date)  AS last_date
         ONE ROW PER MATCH
         PATTERN ( consecutive{9,} final_date )
         DEFINE consecutive as
               purchase_date >= NEXT(TRUNC(purchase_date)) - interval '1' day
           AND purchase_date <  NEXT(TRUNC(purchase_date))
       ) m;


CUSTOMER_ID
FIRST_NAME
LAST_NAME
FIRST_DATE
LAST_DATE
CONSECUTIVE_DAYS


1
John
Doe
2024-03-25 12:59:46.000000
2024-04-08 13:00:00.000000
15