[大数据]SQL牛客的课程订单分析(七)

原题链接

需求

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

步骤一：根据条件需要找出来源信息，找出满足条件的user_id

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

步骤二：将选择的user_id作为条件，从原表中筛选出属于来源信息部分，并根据client_id分组计数

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

步骤三：将步骤二的结果与client表left join，并采用ifnull函数将null值设置为“GrouBuy”，即最终结果

select
    finull(c.name, 'GroupBuy') as source,
    t.cnt
from (
    select
        client_id,
        count(*) as cnt
    from order_info
    where user_id in (
        select
            user_id
        from order_info
        where status = 'completed'
        and product_name in ('C++', 'Java', 'Python')
        and date > '2025-10-15'
        group by user_id
        having count(*) > 1
                      )
    and status = 'completed'
    and product_name in ('C++', 'Java', 'Python')
    and date > '2025-10-15'
    group by client_id
      ) as t
left join client as c
on t.client_id = c.id
order by source

思路来自题解区第二个答案