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

select tt.source,
count(id) as cnt from (
select t.id ,t.is_group_buy,
case when is_group_buy = 'Yes' then 'GroupBuy' else t.client_name end as source
from (select tmp.*,
sum(case when status='completed' then 1 else 0 end) over(partition by user_id) as cnt 
from (select a.* , b.name as client_name from order_info a left join 
client b on a.client_id = b.id ) tmp
where status = 'completed' and date > '2025-10-15' and product_name in ('C++','Java','Python')
) t where t.cnt >= 2 order by id asc
    ) tt group by tt.source order by tt.source

刚开始看可能会觉得奇奇怪怪的，为啥这样写，其实我是根据前几天的思路，copy了一些代码直接上面增加内容写的，所以对我自己而言思路比较清晰而已。