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

select a.user_id,
max(case when a.r=1 then a.date else 0 end) as first_buy_date,
max(case when a.r=2 then a.date else 0 end) as second_buy_date,
a.cnt
from (
      select user_id, date,
       dense_rank() over(partition by user_id order by date) as r,
       count(*) over(partition by user_id) as cnt
       from order_info
       where date > '2025-10-15'
       and status = 'completed'
       and product_name in ('C++','Java','Python')
       ) as a
where a.cnt>=2
group by a.user_id
order by a.user_id