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

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