我更愿意加强我目前的尝试
您可以通过更改第一个CTE 't'查询来获得所需的结果：

select distinct CUSTOMER_ID,
    trunc(PURCHASE_DATE) dat
       from purchases

到

select CUSTOMER_ID,
    trunc(PURCHASE_DATE) dat,
    count(*) cnt
       from purchases
   group by CUSTOMER_ID, trunc(PURCHASE_DATE)

然后将其包括在‘ttt’查询中：

sum(cnt) purchase_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;

fiddle
我建议使用更简单的方法，如@astentx或@MTO的答案所示。

可以使用MATCH_RECOGNIZE：

SELECT p.customer_id,
       c.first_name,
       c.last_name,
       TRUNC(p.first_purchase) AS first_day,
       TRUNC(p.last_purchase) - TRUNC(p.first_purchase) + 1 AS day_count,
       p.purchase_count
FROM   purchases
       MATCH_RECOGNIZE(
         PARTITION BY customer_id
         ORDER BY purchase_date
         MEASURES
           FIRST(purchase_date) AS first_purchase,
           LAST(purchase_date) AS last_purchase,
           COUNT(*) AS purchase_count
         PATTERN (consecutive_days+ final_day)
         DEFINE consecutive_days AS NEXT(purchase_date) < TRUNC(purchase_date) + INTERVAL '2' DAY
       ) p
       INNER JOIN customers c
       ON c.customer_id = p.customer_id
WHERE  p.last_purchase >= TRUNC(p.first_purchase) + 9

对于样本数据，输出：
| 客户ID|联系我们|公司简介|第一天|天数|采购_计数|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 二|丽莎|萨拉迪诺|2019 -04-14 00：00：00|十一|十一|
| 三|迈克尔|帕尔梅什|2019 -03- 22 00：00：00|十五岁|十五岁|
| 三|迈克尔|帕尔梅什|2019 -04-22 00：00：00|二十三|二十三|
| 四个|约瑟夫|扎扎|2019 -01- 21 00：00：00|十三|六十|
如果你真的想使用分析函数和聚合来完成它，那么上面的查询可以转换为：

WITH continuous as (
  SELECT customer_id,
         purchase_date,
         CASE
         WHEN purchase_date < TRUNC(LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date)) + 2
         THEN 0
         ELSE 1
         END AS is_not_continuous
  FROM   purchases p
),
groups as (
  SELECT customer_id,
         purchase_date,
         SUM(is_not_continuous) OVER (PARTITION BY customer_id ORDER BY purchase_date)
          AS grp
 FROM   continuous c
),
totals AS (
  SELECT customer_id,
         TRUNC(MIN(purchase_date)) AS first_day,
         1 + TRUNC(MAX(purchase_date)) - TRUNC(MIN(purchase_date)) AS day_count,
         COUNT(*) AS purchase_count
  FROM   groups
  GROUP BY
         customer_id,
         grp
  HAVING 1 + TRUNC(MAX(purchase_date)) - TRUNC(MIN(purchase_date)) >= 10
)
SELECT c.customer_id,
       c.first_name,
       c.last_name,  
       t.first_day,
       t.day_count,
       t.purchase_count
FROM   customers c
       INNER JOIN totals t
       ON c.customer_id = t.customer_id;

具有相同的输出。
fiddle

您可以使用上述match_recognize并定义匹配模式为 * 在当前行之后的一天内有下一次购买 * 重复9次或更多：第一行应该匹配没有条件（因为我们没有规则的开始系列），然后我们需要有至少9行1天的差距。
下面是purchases表上的代码。该结果可以被联接到其他表以向标识符添加标签等。

with distinct_days as (
  /*Group by day first to count entire days*/
  select
    customer_id,
    product_id,
    trunc(purchase_date) as purchase_date,
    sum(quantity) as quantity
  from purchases p
  group by 
    customer_id,
    product_id,
    trunc(purchase_date)
)
select *
from distinct_days
match_recognize (
  partition by customer_id
  order by purchase_Date asc
  measures
    first(purchase_Date) as first_date,
    last(purchase_Date) as last_Date,
    match_number() as grp,
    count(purchase_Date) as days_,
    sum(quantity) as quantity
--  all rows per match
  pattern(any_ next_day{9,})
  define
    next_day as trunc(purchase_Date) <= trunc(prev(purchase_Date)) + 1
)
order by customer_id, grp

fiddle

这是另一种受DENSE_RANK()启发的方法
dbFiddle Link

-- working 2
        
    with prank as
    (
      select dense_Rank() over (partition by customer_id 
      order by to_number(to_char(purchase_date, 'J'))) - 
      to_number(to_char(purchase_date, 'J')) as recRank, 
      purchase_date, customer_id 
      FROM purchases
    ), 
      prankdays as
    (select customer_id, trunc(max(purchase_date)) - 
               trunc(min(purchase_date)) + 1 as numRunningDays
      min(purchase_date), 
      max(purchase_date)        
    from prank
    group by customer_id, recRank
    having (extract(day from max(purchase_date) - min(purchase_date)) + 1) >=10
    )
    select c.first_name, c.last_name, p.*  
    from prankdays p, customers c 
    where p.customer_id = c.customer_id 
     
    
    -- notes:
    -- 1 extract(day from max(purchase_date) - min(purchase_date)) + 1
    --     doesn't include, therefore trunc is used where both start, end dates inclusive
    -- 2 count(*) records is not applied
    -- 3 dense rank works great for identifying gaps
    -- 4 there are two ways to get number of days between two dates here
    -- 5 a way to convert date to number is shown here
    -- I used the same dbfiddle table schema, data by @AlexPoole (cheers)
    ;

输出：

FIRST_NAME  LAST_NAME   CUSTOMER_ID NUMRUNNINGDAYS  MIN(PURCHASE_DATE)          MAX(PURCHASE_DATE)
Lisa        Saladino    2           11              14-APR-23 01.00.00.000000   24-APR-23 11.00.00.000000
Micheal     Palmice     3           15              02-MAR-22 00.23.05.000000   16-MAR-22 05.46.15.000000
Micheal     Palmice     3           23              22-APR-23 23.00.00.000000   14-MAY-23 01.00.00.000000
Joseph      Zaza        4           13              01-JAN-23 05.00.00.000000   13-JAN-23 12.00.00.000000

一些思考的食物：
Oracle documentation
DENSE_RANK()是Oracle中的一个分析函数，用于计算一组有序行中的一行的排名。返回的秩是从1开始的整数。
与RANK（）函数不同，DENSE_RANK（）函数将秩值作为连续整数返回。在平局的情况下，它不会跳过排名。具有相同等级标准值的行将收到相同的等级值。