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

# 请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序，

with o as (
    select *,
    count(id) over (partition by user_id) as num
    from order_info
    where status = 'completed'
    and product_name in ('c++','java','python')
    and datediff(date,'2025-10-10')>0
)
select 
    (case 
       when is_group_buy='No' then c.name
       else 'GroupBuy'
    end) as source,
    count(o.id) as cnt
from o
left join client c on o.client_id = c.id
where o.num >= 2
group by source
order by source