题解 | #牛客的课程订单分析(五)#
牛客的课程订单分析(五)
http://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427
SELECT t.user_id,
t.date_1 AS first_buy_date,
t1.date_2 AS second_buy_date,
t.cnt AS cnt
FROM
(
SELECT user_id,
MIN(date) AS date_1,
COUNT(*) AS cnt
FROM order_info
WHERE
date > "2025-10-15"
AND status = "completed"
AND product_name IN ("C++", "Python", "Java")
GROUP BY user_id
HAVING cnt > 1
) AS t
LEFT JOIN
(
SELECT user_id,
MIN(date) AS date_2
FROM order_info
WHERE
date > "2025-10-15"
AND status = "completed"
AND product_name IN ("C++", "Python", "Java")
AND (user_id, date) NOT IN (SELECT user_id, MIN(date) FROM order_info WHERE date > "2025-10-15" AND status = "completed" AND product_name IN ("C++", "Python", "Java") GROUP BY user_id )
GROUP BY user_id
) AS t1
ON t.user_id = t1.user_id
ORDER BY t.user_id
