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

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
    t3.source,count(t3.id) as cnt
from
    (select
        case
            when t.is_group_buy='No' then c.name 
            else 'GroupBuy' end as source,
        t.id
    from t left join client c
    on t.client_id=c.id
    order by t.id) t3
group by t3.source
order by t3.source