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

select name source, count(*) cnt
from (
    # 筛选出num大于等于2的记录
    select client_id
    from (
        # 使用窗口函数来生成一个新列记录数目
        select user_id, product_name, client_id, is_group_buy, 
        count(1)over(partition by user_id) num
        from order_info
        where datediff(`date`, '2025-10-15') > 0
        and status = 'completed'
        and product_name in ('C++', 'Java', 'Python')
    ) t1
    where num > 1
) t2
join (
    select * from client
    union 
    select 0, 'GroupBuy'
) t3
on t2.client_id = t3.id
group by name
order by source;