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

with rowrank as 
(
select *,
       count(id) over(partition by user_id) as tcount,
       row_number() over(partition by user_id) as trank 
from order_info
where product_name in ('C++','Python','Java')
and status = 'completed'
and date > '2025-10-15')

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