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

第一种：使用开窗函数

select r.client_name as source, count(r.id)
from(
    select t.id, t.is_group_buy,
    (case when t.is_group_buy="No" then c.name else 'GroupBuy' end) as client_name
    from (
        select *,
        count(*) over(partition by user_id) as cnt_course
        from order_info
        where date>=2025-10-15 
            and status='completed'
            and product_name in ('C++','Python','Java')
    )as t
    left join client c on c.id=t.client_id
    where t.cnt_course>=2
    order by t.id
) as r
group by r.client_name 
order by source

第二种：未使用开窗函数

select source, count(t.user_id) as cnt
from(
    select user_id, c.id, product_name, c.name, 
    case  is_group_buy 
        when 'No' then c.name
        when 'Yes' then 'GroupBuy'
        end as source
    from order_info o
    left join client c on c.id=o.client_id
    where date>=2025-10-15 
        and status='completed'
        and product_name in ('C++','Python','Java')
        and user_id in (
            select user_id
            from order_info
            where date>=2025-10-15 
                   and status='completed'
                   and product_name in ('C++','Python','Java')
            group by user_id
            having count(*)>=2

        )
) t
group by t.source 
order by source