窗口函数NTH_VALUE

select distinct a.user_id,a.d1,a.d2,b.cnt from (select user_id,date, NTH_VALUE(date,1) over(partition by user_id order by STR_TO_DATE(date,'%Y-%m-%d')) d1, NTH_VALUE(date,2) over(partition by user_id order by STR_TO_DATE(date,'%Y-%m-%d')) d2 from order_info where status='completed' and product_name in('C++','Java','Python') and STR_TO_DATE(date,'%Y-%m-%d')>STR_TO_DATE('2025-10-15','%Y-%m-%d')) a JOIN (select user_id,count(*) cnt from order_info where status='completed' and product_name in('C++','Java','Python') and STR_TO_DATE(date,'%Y-%m-%d')>STR_TO_DATE('2025-10-15','%Y-%m-%d') group by user_id ) b on a.user_id=b.user_id and a.d2 is not null