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

思路：①先在原表date后面增加一列下偏移日期，即把后一次购买的日期移到本行记为r，用窗口函数lead，形成表tb1；
②在tb1基础上选出最小日期min(date)、最小后一次购买日期min(r)即可。
select user_id, min(date), min(r), min(cnt)
from(
select user_id, date, 
lead(date,1)over(partition by user_id order by date) as r,
count(user_id)over(partition by user_id) as cnt
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')) as tb1
where cnt>=2
group by user_id
order by user_id