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

with t as(
    select
        t1.*
    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
    t.id,t.is_group_buy,
    case
        when t.is_group_buy='No' then c.name 
        else 'None' end as client_name
from t left join client c
on t.client_id=c.id
order by t.id