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

用了四个开窗 但思路应该很清晰

select user_id,first_buy_date,second_buy_date,cnt
from
    (select  user_id,
    first_value(date)over(partition by user_id order by date) as first_buy_date,
    nth_value(date,2)over(partition by user_id order by date) as second_buy_date,
    count(1)over(partition by user_id ) as cnt,
    row_number()over(partition by user_id order by date) as rn
    from order_info
    where  date > '2025-10-15' and product_name in("C++","Java","Python") and status = "completed"
    )t1
where rn = 2
order by user_id