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

select
    user_id,
    min(date) as first_buy_date,
    max(date) as second_buy_date,
    cnt
from
    (
        select
            *,
            count(product_name) over (partition by user_id) as cnt,
            row_number() over (partition by user_id order by date) as buy_date_rank
        from
            order_info
        where
            status = 'completed'
            and product_name in ('C++', 'Java', 'Python')
            and date > '2025-10-15'
    ) a
where cnt >= 2 and buy_date_rank <= 2
group by    
    user_id, cnt
order by
    user_id