首先建个新表temp把条件限制后的原表存下来，并用rwo_number()和count()搭配窗口函数根据user_id分组来增加组内排序ranking和每组总数量cnt。ranking根据时间对每个user买课记录进行排序标号，cnt是统计一个user总共有多少条符合标准的买课记录。

with temp as
(select user_id, date, 
row_number() over (partition by user_id order by date) as ranking,
count(id) over (partition by user_id) as cnt
from order_info
where (product_name = "C++" or product_name = "Java" or product_name = "Python")
      and date > "2025-10-15" and status = "completed")

然后两表对查，通过子查询从temp中找出对应当前主查询user_id的date并限制ranking等于1或者2，以此提取出第一次和第二次购买的日期。然后限制cnt>=2

select distinct t.user_id, 
(select t1.date from temp as t1 
 where t.user_id = t1.user_id and t1.ranking = 1) as first_buy_date,
(select t2.date from temp as t2 
 where t.user_id = t2.user_id and t2.ranking = 2) as second_buy_date,
t.cnt
from temp as t
where t.cnt >= 2

完整答案如下。

with temp as
(select user_id, date, 
row_number() over (partition by user_id order by date) as ranking,
count(id) over (partition by user_id) as cnt
from order_info
where (product_name = "C++" or product_name = "Java" or product_name = "Python")
      and date > "2025-10-15" and status = "completed")

select distinct t.user_id, 
(select t1.date from temp as t1 
 where t.user_id = t1.user_id and t1.ranking = 1) as first_buy_date,
(select t2.date from temp as t2 
 where t.user_id = t2.user_id and t2.ranking = 2) as second_buy_date,
t.cnt
from temp as t
where t.cnt >= 2