前半段使用了row_number函数,计算出用户购买日期的排序,后续筛选出购买课程大于1的客户

with t as(
select *
,count(*)over(partition by user_id) as cnt
,row_number()over(partition by user_id order by date) as numb
from order_info
where date >'2025-10-15'
and product_name in('C++','Java','Python')
and status='completed')

下半段借助大佬的分享修改的代码,使用了case when 当row_number=1时,输出date对应的值并把名修改为first_buy_date,row_number=2时同理,case when旁边为什么要加min这个我一开始是很不理解的,但是自己一看group by了user_id,自然其他都要加上聚合函数才行;

select user_id
,min(case when numb ='1' then date end) first_buy_date
,min(case when numb ='2' then date end) second_buy_date
,count(*) as cnt
from t
where t.cnt>1
group by user_id
order by user_id