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

使用first_value()\nth_value(),count()三个窗口函数即可

select user_id,first_buy_date,second_buy_date,cnt from
(
select distinct user_id,
first_value(date) over(partition by user_id order by date) first_buy_date,
nth_value(date,2) over(partition by user_id order by date) second_buy_date,
count(*) over(partition by user_id) cnt
from order_info
where status='completed'
and date>='2025-10-15'
and product_name in('c++','python','java')
)a
where second_buy_date is not null and first_buy_date is not null
order by user_id