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

'3，以来源分组，聚合函数count求订单数cnt
select t2.source,count(source) cnt 
from(
    '2，筛选出购买2单及以上的用户的数据行，再加来源列，将拼单行设为GroupBuy
    select t1.*,if(t1.is_group_buy='NO',c.name,'GroupBuy') source
    from 
         '1，返回符合条件的数据行，并利用开窗函数可以返回每行的特性，计算每个用户购买成功订单数，再和client表连接
          (select *,count(user_id) over(partition by user_id) order_num
          from order_info
          where date>='2025-10-15'and status='completed'and product_name in ('C++','Java','Python')
          ) t1 left join client c on t1.client_id=c.id
    where t1.order_num>=2
) t2
group by t2.source
order by t2.source;