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

select id,user_id,product_name,status,client_id,date
from
(SELECT *,
COUNT(user_id) over (partition by user_id) as r
FROM order_info
WHERE date > '2025-10-15'
  AND status = 'completed'
  AND product_name IN ('C++', 'Python', 'Java')
order by id) as a
where a.r>=2
首先明确需要显示基于user_id次数筛选的所有字段，所以选择创建COUNT(user_id) over (partition by user_id) as r 的窗口函数
接着在以上新增窗口函数的基础上，基于子查询结果，筛选user_id次数不小于2的所有字段