题解 | 丝滑

select t1.user_id user_id,t1.date first_buy_date,t2.date second_buy_date,t1.cn cnt
from(select user_id,product_name,date,cn
from(select *,row_number()over(partition by user_id order by date) rn
from(select *,count(user_id)over(partition by user_id) cn
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')) t 
where cn>=2) t
where rn=1) t1
left join
(select user_id,product_name,date
from(select *,row_number()over(partition by user_id order by date) rn
from(select *,count(user_id)over(partition by user_id) cn
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')) t 
where cn>=2) t
where rn=2) t2
on t1.user_id=t2.user_id