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

一开始想复杂了，其实很简单，由于需要统计每个用户的购买次数，又要保留每条记录的client_id，所以使用不会改变行数的窗口函数进行分组count，以上作为一个子查询。然后在父查询对count的结果做筛选，以及进行group by分组count

WITH a AS(
    SELECT client_id AS id,COUNT(user_id) OVER (PARTITION BY user_id) AS 'cbu'
    FROM order_info
    WHERE date>'2025-10-15' AND status='completed' AND product_name IN ('C++','Python','Java')
)
SELECT IFNULL(name,'GroupBuy') AS source,COUNT(id) AS cnt
FROM a LEFT JOIN client USING(id)
WHERE cbu>1
GROUP BY id
ORDER BY source