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

select a.user_id,min(a.date) first_buy_date,max(a.date) second_buy_date,a.cnt from (
    select
        id, user_id, product_name, status, client_id, date,
        rank() over(partition by user_id order by date) as cnt1,
        count(*) over(partition by user_id) as cnt
    from order_info
    where status = 'completed' and product_name in ('C++','Java','Python') and date > '2025-10-15') a where a.cnt>1 and a.cnt1<3
group by a.user_id,a.cnt order by a.user_id