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

with t1 as (
    select id, user_id, client_id, is_group_buy
    from order_info
    where date >= '2025-10-15'
    and status = 'completed'
    and product_name in ("C++", "Java", "Python")
),
t2 as (
    select * 
    from t1
    where user_id in (
        select user_id
        from t1
        group by user_id
        having count(id) >= 2
    )
)
select *
from (
    (select "GroupBuy" as source, count(id) as cnt
    from t2
    where is_group_buy = "Yes")
    union all
    (select a.name as source, count(t2.id) as cnt
    from t2
    left join client as a
    on a.id = t2.client_id
    where t2.is_group_buy = "No"
    group by a.name)
) as b
order by source;