题解 | #牛客的课程订单分析(三)#count窗口

方法一：内表找出user_id，外表找出该user_id符合的记录

with moreThan1 as(
    select user_id
    from order_info
    where product_name in ('C++','Java','Python')
    and status = 'completed' and date > '2025-10-15'
    group by user_id
    having count(*) >= 2
)

select *
from order_info
where user_id in (select * from moreThan1)
and product_name in ('C++','Java','Python')
and status = 'completed' and date > '2025-10-15'
order by id

方法二：count聚合窗口函数

select t1.id, t1.user_id,t1.product_name,t1.status,t1.client_id,t1.date
from
(
    select *,count(id)over(partition by user_id) as number
    from order_info
    where datediff(date,"2025-10-15")>0
      and status ="completed"
      and product_name in ("C++","Java","Python")
) t1
where t1.number >1
order by t1.id