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

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