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

感觉有点 繁琐了

SELECT t2.user_id,t2.lagdate first_buy_date,t2.date second_buy_date,t2.rk cnt FROM ( SELECT *, row_number() OVER (PARTITION BY user_id) rk2 FROM ( SELECT *, count(id) OVER (PARTITION BY user_id) rk, lag(date,1,0000-00-00) OVER (PARTITION BY user_id ORDER BY date) lagdate FROM order_info WHERE datediff(date,'2025-10-15')>0 AND status ='completed' AND product_name in ('C++','Java','Python') )t1 WHERE t1.rk > 1 )t2 WHERE t2.rk2 = 2;