题解 | #考试分数(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
记录：初始想法：比较复杂，想到的是把第二小的日期做链接
SELECT o1.user_id, MIN(o1.date) AS 'first_buy_date', o2.date AS 'second_buy_date', COUNT(id) AS 'cnt'
FROM order_info o1 LEFT JOIN
(SELECT user_id, date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) 'tmp' FROM order_info
WHERE date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) o2
ON o1.user_id = o2.user_id AND tmp = 2
WHERE o1.date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++', 'Java', 'Python')
GROUP BY user_id
HAVING COUNT(id) >= 2
ORDER BY user_id
看了别人的题解。。
SELECT user_id, MIN(date), MAX(date), cnt
FROM (SELECT user_id,
date,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) AS 'tmp',
COUNT(id) OVER(PARTITION BY user_id) AS 'cnt'
FROM order_info
WHERE date > '2025-10-15' AND status = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) o
WHERE cnt >= 2 AND tmp <= 2
GROUP BY user_id
ORDER BY user_id
