首先分组,时间排序,

去掉次数为1的,然后俩表链接(同一用户,时间排序为1和2的链接)

再做个总次数的表,链接起来)。

group by user_id

select d.user_id,first,second,cnt from
(select a.user_id,a.date as first,b.date as second from
(select user_id,date,
rank() over(partition by user_id order by str_to_date(date,"%Y-%m-%d")) as rk
from order_info
where status="completed"
and datediff(str_to_date(date,"%Y-%m-%d"),str_to_date("2025-10-15","%Y-%m-%d"))>0
and product_name in ("C++","Python","Java"))a
join
(select user_id,date,
rank() over(partition by user_id order by str_to_date(date,"%Y-%m-%d")) as rk
from order_info
where status="completed"
and datediff(str_to_date(date,"%Y-%m-%d"),str_to_date("2025-10-15","%Y-%m-%d"))>0
and product_name in ("C++","Python","Java"))b
on a.rk=1 and b.rk=2 and a.user_id=b.user_id
)d
join
(select user_id,max(rk) as cnt from
(select user_id,date,
rank() over(partition by user_id order by str_to_date(date,"%Y-%m-%d")) as rk
from order_info
where status="completed"
and datediff(str_to_date(date,"%Y-%m-%d"),str_to_date("2025-10-15","%Y-%m-%d"))>0
and product_name in ("C++","Python","Java")
) as c
group by user_id
)e
on d.user_id=e.user_id