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

select source,count(*) cnt from 
(select 
case e.is_group_buy when 'Yes' then 'GroupBuy' else e.name end source
from 
(select oi.is_group_buy,c.name,count(*)over(partition by oi.user_id) ct
from order_info oi left join client c on oi.client_id = c.id 
where datediff(oi.date,'2025-10-15')>0 and oi.status='completed' and oi.product_name in ('C++','Python','Java')) e  
where e.ct >= 2) s
group by s.source
order by source
此题可以取巧，由于拼团订单对应的客户端id是0，0的客户端是没有名称的，故根据名称聚合，可将null组设置为GroupBuy，其余对应相应名称