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

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