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

首先还是根据条件限制从原表中选出temp并加上根据user_id分组后每组的count。

然后将temp和client表通过left join连接并限制cnt>=2。用if进行条件判断，在client name中补齐groupbuy。然后count()搭配窗口函数增加一列以client_id分组每一组的数量。最后select的时候要使用distinct，因为使用聚合函数count()的时候并没有搭配group by,至于为什么没用group by，是因为窗口函数顺手写了懒得改了。。。

with temp as
(select id, user_id, date, client_id, 
count(id) over (partition by user_id) as cnt1
from order_info
where (product_name = "C++" or product_name = "Java" or product_name = "Python")
      and date > "2025-10-15" and status = "completed")
    
select distinct
if(t.client_id=0, "GroupBuy", c.name) as source, 
count(t.id) over (partition by t.client_id) as cnt
from temp as t left join client as c
on c.id = t.client_id
where t.cnt1 >= 2
order by source