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

SELECT DISTINCT user_id,first_buy_date,DATE AS second_buy_date,cnt
FROM
(SELECT *
       ,COUNT(product_name) OVER(PARTITION BY user_id) AS cnt
       ,MIN(DATE) OVER(PARTITION BY user_id) AS first_buy_date
       ,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY DATE) AS rk
FROM order_info
WHERE DATE>='2025-10-15' AND STATUS = 'completed'
AND product_name IN ('C++','Java','Python')
) AS t1
WHERE cnt>=2 AND rk=2
ORDER BY user_id ASC

对每一个user_id的购买情况进行排序即可