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

#初版
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;

#优化
select
user_id
,max(if(rn=1,date,0)) as first_buy_date
,max(if(rn=2,date,0)) as second_buy_date
,cnt
from
    (select
    user_id
    ,date
    ,row_number() over(partition by  user_id order by date) as rn
    ,count(*)over(partition by user_id) as cnt
    from
        order_info
    where
    product_name in('C++','Python','Java')
    and status='completed'
    and date >'2025-10-15') a
where a.cnt>=2
group by 1,4