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

select a.user_id user_id,min(date) as first_buy_date,
       max(date) as second_buy_date,a.cnt cnt
from (select t.user_id user_id,t.date date,
          row_number()over(partition by t.user_id order by t.date) rk,
          count(*)over(partition by t.user_id) as cnt
      from (select *
            from order_info
            where date>'2025-10-15' and `status`='completed' and 
                 product_name in ('C++','Java','Python')) t ) a 
where a.cnt>1 and rk<3
group by a.user_id
order by user_id