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

第一步用where和窗口函数筛选出购买状态、日期、购买次数等，即
(select *,
count(user_id) over(partition by user_id) as num
from
order_info as oi
where product_name in ('C++','Python','Java')
and date>2025-10-15
and status='completed') as a
第二步，基于第一步结果左连接client表格，并用where和case when函数选出购买次数大于2，并且不属于团购的重命名，即where a.num>=2，(case when a.client_id != 0 then c.name else 'GroupBuy' end) as source
第三步，易错点，基于 case函数和前面步骤的筛选结果，对于客户端分组技术，即
count(a.client_id) as cnt
最后，因为select使用了聚合函数count，别忘了对source进行group by并排序，整体输出
select 
(case when a.client_id != 0
        then c.name
        else 'GroupBuy'
        end) as source,
count(a.client_id) as cnt
from
(select *,
count(user_id) over(partition by user_id) as num
from
order_info as oi
where product_name in ('C++','Python','Java')
and date>2025-10-15
and status='completed') as a
left join client as c
on a.client_id=c.id
where a.num>=2
group by source
order by source