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

select
    a1.user_id,
    a1.first_buy_date,
    a2.second_buy_date,
    a3.cnt
from (
    select
        t1.user_id,
        t1.date first_buy_date
    from (
        select
            user_id,
            date,
            row_number() over(partition by user_id order by date) rn
        from order_info
        where date > '2025-10-15' and product_name in ('C++','Java','Python') and status = 'completed'
    ) t1
    where rn = 1
) a1
-- rn = 1 inner join rn = 2 ：自然可以满足用户下单2个以及2个以上
inner join (
    select
        t1.user_id,
        t1.date second_buy_date
    from (
        select
            user_id,
            date,
            row_number() over(partition by user_id order by date) rn
        from order_info
        where date > '2025-10-15' and product_name in ('C++','Java','Python') and status = 'completed'
    ) t1
    where rn = 2
) a2
on a1.user_id = a2.user_id
inner join (
    -- 2025-10-15以后状态为购买成功的C++课程或Java课程或Python课程，各用户购买总次数
    select
        user_id,
        count(id) cnt
    from order_info
    where date > '2025-10-15' and product_name in ('C++','Java','Python') and status = 'completed'
    group by user_id
) a3
on a1.user_id = a3.user_id
order by user_id ;