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

/*
1.2025-10-15以后，同一个用户下单2个以及2个and status=completed and product_name in(C++,Python,Java)
2.符合1的客户端，
3.2得出的客户端对应的总订单
*/


# order_info 肯定是要和client连接，并且cliend_id=0的要将他改成GroupBuyf


select
t1.app_name as source,
count(t1.sta) as cnt
from
(select
o.status as sta,
ifnull(c.name,'GroupBuy') as app_name
,count(o.status)over(partition by o.user_id ) as cnt
from order_info o left join client c
on o.client_id = c.id
where o.product_name in('C++','Java','Python') and o.status='completed'  and o.date > '2025-10-15') as t1
where t1.cnt>=2
group by t1.app_name
order by source

ifnull(字段，‘default value’) 可以直接修改字段里面的值