Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY  HH24:MI:SS.FF';
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 customer_id, 102 product_id, 1 quantity,
             DATE '2024-04-08' + INTERVAL '13' HOUR + ((LEVEL-1) * INTERVAL '1 00:00:01' DAY TO SECOND)  * -1
           as purchase_date
    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, 102, 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 * 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(P{10,})
    define P as next(purchase_date)=purchase_date + interval '1' day
);
select * 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(P{10,})
    define P as next(purchase_date) >= trunc(purchase_date) + interval '1' day
      and next(purchase_date) < trunc(purchase_date) + interval '2' day
);


CUSTOMER_ID
FIRST_DATE
LAST_DATE


1
25-MAR-2024  12:59:46.000000
07-APR-2024  12:59:59.000000