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

with t1 as (
select *
from order_info a 
where a.product_name in ('Python','C++','Java')
and status='completed'
and date(date)>='2025-10-15'
)

select t2.user_id,t2.date first_buy_date,
t2.rnn second_buy_date	,t2.rb as cnt
from(
select *,row_number() over(partition by user_id order by date) rn,
lead(date,1) over(partition by user_id order by date) rnn,
count(id) over(partition by user_id ) rb
from t1
)t2
where t2.rn=1
and t2.rb >=2
order by t2.user_id