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

#创建结果表中的source字段
select
(case 
    when is_group_buy="Yes" 
    then "GroupBuy"
    else name
    end) as source
, count(t12.id) as cnt #count(*)会把null值也计入

from
    (
    select 
    count(t1.id) over (partition by user_id) as cnt #创建筛选条件“每个用户的订单数”，用窗口函数代替group by以避免出错。如果用group by，则select的所有变量需要在聚合函数或group by中。

    ,t1.id, t1.is_group_buy, t2.name #外层循环需要
    from order_info t1
    left join client t2
    on t1.client_id=t2.id

    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Java','Python')
    ) t12

where t12.cnt>=2 #对子查询中新建字段的where筛选需要放在子查询的外部
group by source
order by source
;