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

SELECT user_id,
MIN(date) AS 'first_buy_date',
MAX(date) AS 'second_buy_date', 
cnt
FROM
(SELECT *, 
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY date ASC) AS 't_rank',
COUNT(*) OVER(PARTITION BY user_id) AS 'cnt'
FROM order_info
WHERE status = 'completed' AND 
product_name IN ('Python','C++','Java') AND
date > '2025-10-15') t
WHERE cnt >= 2 AND t_rank <= 2
GROUP BY user_id
ORDER BY user_id;