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

1. 查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的用户id

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

2. 查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的 所有信息

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

3. 将2作为新表和client表连接， 通过group by和case when 得到结果

select (case when name is null then 'GroupBuy' else name end) as source ,count(*)
from (
    select *
    from order_info
    where user_id in (
        select user_id 
        from order_info
        where date>'2025-10-15'
        and status ='completed'
        and product_name in ('C++','Java','Python')
        group by user_id
        having count(*)>=2
    )
    and date>'2025-10-15'
    and status ='completed'
    and product_name in ('C++','Java','Python')
) order_new
left join client c
on order_new.client_id = c.id
group by (case when name is null then 'GroupBuy' else name end) 
order by source asc