知识点

1. 窗口函数表格和之前一样写,因为之后有日期可能同一天买不同的课所以这里使用dense_rank，然后把新表按照id进行排序
2. 第一天第二天或者以此类推，使用case when，第一天就是r=1对应的日期没有符合条件的就是0，取结果的最大值

代码

select a.user_id, 
max(case when a.r=1 then a.date else 0 end) as first_buy_date, 
max(case when a.r=2 then a.date else 0 end) as second_buy_date, 
a.cnt
from (
      select user_id, date,
       dense_rank() over(partition by user_id order by date) as r,
       count(*) over(partition by user_id) as cnt
       from order_info
       where date > '2025-10-15'
       and status = 'completed'
       and product_name in ('C++','Java','Python')
       ) as a
where a.cnt>=2
group by a.user_id
order by a.user_id