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

-- case when 牛!

select
    user_id,
    min(
        case
            when r_n = 1 then date
        end
    ) as first_buy_date,
    min(
        case
            when r_n = 2 then date
        end
    ) as second_buy_date,
    cnt
from
    (
        select
            user_id,
            date,
            ROW_NUMBER() over (
                PARTITION by
                    user_id
                order by
                    date
            ) as r_n,
            count(*) over (
                partition by
                    user_id
            ) as cnt
        from
            order_info
        where
            date > '2025-10-15'
            and product_name in ('C++', 'Java', 'Python')
            and status = 'completed'
    ) a
where
    cnt >= 2
    
group by
    user_id
order by
    user_id