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

select
a.user_id
,b.first_buy_date
,a.second_buy_date
,b.cnt
from
    (select  #求second_buy_date
    user_id
    ,date as second_buy_date
    from
        (select   #日期筛选排序
        user_id
        ,date
        ,row_number() over(partition by  user_id order by     date) as rn
        from
            order_info
        where
        product_name in('C++','Python','Java')
        and status='completed'
        and date >'2025-10-15')c
    where rn=2 )a 
    join
    (select     #求次数
    user_id
    ,min(date) as first_buy_date
    ,count(*) cnt
    from
        order_info
    where
    product_name in('C++','Python','Java')
    and status='completed'
    and date >'2025-10-15'
    group by 1) b
    on a.user_id=b.user_id
order by 1;