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

SELECT 
x.user_id,
min(date) AS "first_buy_date",
max(date) AS "second_buy_date",
x.cnt
FROM (SELECT 
oi.id,          
oi.user_id,
oi.date,      
ROW_NUMBER()over(partition by oi.user_id order by oi.date)mm,
count(*)over(partition by oi.user_id) cnt
FROM order_info oi
where datediff(oi.date,"2025-10-15")>0
  and oi.status ="completed"
  and oi.product_name in("C++","Java","Python"))x
  WHERE x.cnt>=2 and x.mm<=2
  group by x.user_id,x.cnt
  order by x.user_id