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

select
    user_id
    ,date first_buy_date
    ,dt as second_buy_date
    ,cnt 
from
    (
        SELECT
            user_id,
            date,
            lead(date,1,date)over(partition by user_id order by date ASC) as dt,
            dense_rank() over (
                    partition by
                        user_id
                    order by
                        date ASC
                ) as rk,
            count(date) 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'
    ) as t 
    where cnt > 1
and rk=1
order by user_id ASC;