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

with
t1 as(
    select
        user_id,
        count(user_id)over(partition by user_id) as course_cnt,
        date,
        dense_rank()over(partition by user_id order by date) as date_rank
    from
        order_info
    where
        status='completed'
        and
        product_name in ('C++','Java','Python')
        and
        date>'2025-10-15'
),
t2 as(
    select
        user_id,
        date as first_buy_date,
        course_cnt as cnt
    from
        t1
    where
        date_rank=1
),
t3 as(
    select
        user_id,
        date as second_buy_date
    from
        t1
    where
        date_rank=2
)

select
    t2.user_id as user_id,
    first_buy_date,
    second_buy_date,
    cnt
from
    t2 left join t3 using(user_id)
where
    cnt>=2
order by
    user_id