题解 | #牛客的课程订单分析(七)#
牛客的课程订单分析(七)
https://www.nowcoder.com/practice/d6f4a37f966145da8900ba9edcc4c068
一开始想复杂了,其实很简单,由于需要统计每个用户的购买次数,又要保留每条记录的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