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

with t3 as(
    select
        t1.*,
        dense_rank() over(partition by t1.user_id order by t1.date asc) as dtrank
    from order_info t1
    inner join
        (select user_id
        from order_info
        where
            status='completed'
            and product_name in('C++','Java','Python')
            and date>'2025-10-15'
        group by user_id
        having count(id)>=2) t2
    on t1.user_id=t2.user_id
    where 
        t1.status='completed'
        and t1.product_name in('C++','Java','Python')
        and t1.date>'2025-10-15')

select
    user_id,
    max(case when dtrank=1 then date else 0 end) as first_buy_date,
    max(case when dtrank=2 then date else 0 end) as second_buy_date,
    count(id) as cnt
from t3
group by user_id
order by user_id