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

加油！

SELECT a.user_id,MIN(date) AS first_buy_date,MAX(date) AS second_bud_date,MAX(a.num) AS cnt
  FROM 
(SELECT user_id,date,DENSE_RANK() OVER(PARTITION BY user_id ORDER BY date ) AS rk,
        COUNT(*) OVER(PARTITION BY user_id) AS num
 FROM order_info 
  WHERE date > '2025-10-15' 
        AND (product_name = 'C++' OR product_name = 'Python' OR product_name='Java')
        AND status = 'completed' 
        ) a
 WHERE a.rk <=2 AND  a.num >= 2
 GROUP BY a.user_id
 ORDER BY user_id