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

-- 使用case when 或者 if，内部使用窗口函数
select
case when t1.is_group_buy = 'Yes' then 'GroupBuy' else c.name end source,
count(*) as cnt
from client c
right join
(
select *,
count(id)over(partition by user_id) as number
from order_info
where datediff(date,'2025-10-15')>1
and status = 'completed'
and product_name in ('C++','Java','Python')
) t1
on t1.client_id = c.id
where t1.number > 1
group by source
order by source;