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

with
t1 as(
    select
        user_id,
        product_name,
        status,
        date,
        count(status)over(partition by user_id) as counts
    from
        order_info
    where
        status='completed'
        and
        product_name in ('C++','Java','Python')
        and
        timestampdiff(day,'2025-10-15',date)>0
),
t2 as(
    select distinct
        user_id
    from
        t1
    where
        counts>=2
    order by
        user_id
)

select
    *
from
    order_info
where
    exists (select 1 from t2 where t2.user_id = order_info.user_id)
    and
    status='completed'
    and
    product_name in ('C++','Java','Python')
    and
    timestampdiff(day,'2025-10-15',date)>0
order by
    id