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

首先还是新建个表temp来存条件筛选之后的原表并加上根据user_id分组后每组的count。
然后将temp和client用left join连接，并限制每组count大于等于2.然后用if判断client_id是否为0，client_id=0说明is_group_buy=Yes，这种情况下client name就是null，反之就是正常client name。

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