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

/*
    select
                user_id
                ,case when t1.is_group_buy = 'NO' then t2.name else 'GroupBuy' end as     `source`
                ,count(*) over (partition by t1.user_id) as 'num'
    from
                    order_info  t1
        left join   client  t2  on t1.client_id = t2.id and t1.product_name in ('C++','Python','Java')
        and t1.date >= '2025-10-15' and t1.status = 'completed'
*/
select distinct
*
from
(
select
        case when is_group_buy = 'No' then t2.name else 'GroupBuy' end  as   `source`
        ,count(*) over (partition by t1.client_id) as   `cnt`
from
    order_info t1
    left join   client t2  on t1.client_id = t2.id
where
        t1.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
        ) and   t1.date >= '2025-10-15' and t1.status = 'completed' and t1.product_name in ('C++','Python','Java')
) t4        
order by t4.source