SQL实战83：牛客的课程订单分析(七)

题目

解答

with tmp as (
select user_id,count(*) as cnt
from order_info
where date > "2025-10-15"
and product_name in ("C++","Java","Python")
and status = "completed"
group by user_id
having cnt >= 2 )

select if(t.name is null,"GroupBuy",t.name) as source
       ,count(t.id) as cnt
from 
    (select o.id,o.is_group_buy,c.name
    from order_info as o
    left join client as c
    on o.client_id = c.id
    where o.date > "2025-10-15"
    and o.product_name in ("C++","Java","Python")
    and o.status = "completed"
    and o.user_id in (select user_id from tmp)
    order by o.id) t 
group by t.name
order by source ;