题解 | #牛客的课程订单分析(六)#
牛客的课程订单分析(六)
http://www.nowcoder.com/practice/c5736983c322483e9f269dd23bdf2f6f
#第一步：按照条件筛选全部数据
#第二步：窗口函数-统计两个及以上成功的订单,左连接客户端表
#第三步：根据步骤二的表来做筛选即可
SELECT * FROM order_info WHERE DATE > "2025-10-15" AND STATUS = "completed" AND product_name IN ("C++","Java","Python")
#第二步：窗口函数-统计两个及以上成功的订单,左连接客户端表
SELECT t.id, t.user_id, t.client_id, t.is_group_buy, cl.name,
COUNT(STATUS) over(PARTITION BY user_id ORDER BY STATUS) "cnt"
FROM
(SELECT * FROM order_info WHERE DATE > "2025-10-15" AND STATUS = "completed" AND product_name IN ("C++","Java","Python"))t
LEFT JOIN `client` cl
ON t.client_id = cl.id
#第三步：根据步骤二的表来做筛选即可
SELECT id, is_group_buy, client_name
FROM
(SELECT t.id, t.user_id, t.client_id, t.is_group_buy, cl.name "client_name",
COUNT(STATUS) over(PARTITION BY user_id ORDER BY STATUS) "cnt"
FROM
(SELECT * FROM order_info WHERE DATE > "2025-10-15" AND STATUS = "completed" AND product_name IN ("C++","Java","Python"))t
LEFT JOIN `client` cl
ON t.client_id = cl.id) tt
WHERE cnt >= 2
ORDER BY id
查看30道真题和解析
