题解 | 牛客的课程订单分析(五)

select t2.user_id,first_buy_date,second_buy_date,cnt from(
    
(select user_id,min(date) first_buy_date,count(*) cnt from order_info
where product_name in ("C++","Java","Python")
and status = "completed"
and date > "2025-10-15"
group by user_id
having count(*) >= 2
order by user_id) as t2

left join 

(select t1.user_id uid,date second_buy_date from
(select id,user_id,dense_rank()over(partition by user_id order by date) ranking from order_info
where product_name in ("C++","Java","Python")
and status = "completed"
and date > "2025-10-15"
) as t1 inner join order_info oi on t1.id = oi.id and ranking = 2) as t3
on t2.user_id = t3.uid
)

order by t2.user_id

自己想的比较复杂了，一会看看评论区的简单解法再做一遍，但好歹是自己写的，发出来记录一下