with t2 as(
select user_id , date as first_buy_date
from (
select user_id, date,
rank()over(partition by user_id  order by date) as rank1
from order_info
where 
date > '2025-10-15'
and status = 'completed'
and product_name in ('Python','Java','C++')
) t1 
where rank1 = 1
),
t3 as(select user_id, count(id) as cnt
from order_info 
where date > '2025-10-15'
and status = 'completed'
and product_name in ('Python','Java','C++')
group by user_id)
select user_id ,  first_buy_date, date as second_buy_date, cnt
from (
select user_id, date,
rank()over(partition by user_id  order by date) as rank1
from order_info
where 
date > '2025-10-15'
and status = 'completed'
and product_name in ('Python','Java','C++')
) t1 
join t2 using(user_id)
join t3 using(user_id)
where rank1 = 2

不断连接表