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

with new as
(
select
*
,count(status)over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
)
, new1 as
(
select
*
from new
where cnt>=2
)
, new2 as
(
select
coalesce(c.name,'GroupBuy') as source #这样只是将NULL换作group by而已
#,cnt 这个cnt是一个用户有多少订单，不符合题目要求的这个客户端有多少订单
from new1 n left join client c
on n.client_id = c.id
)

select
source
,count(*)
from new2
group by source
order by source asc