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

select a.user_id,min((case when r=1 then a.date end)) first_buy_date, min((case when r=2 then a.date end)) second_buy_date, count(a.user_id) cnt
from 
       (select user_id, date, row_number()over(partition by user_id order by date asc) r 
        from order_info
        where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
       ) a #a表为符合要求并按日期排名的信息表
group by a.user_id
having cnt>=2
order by a.user_id asc