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

select 
t.user_id
,min(t.date) as first_buy_date
,max(t.date) as second_buy_date
,t.cnt
from 
(
select 
user_id
,date
,rank() over(partition by user_id order by date) r_no
,count(*) over(partition by user_id) cnt
from order_info
where user_id in 
(
    select user_id 
    from order_info a
    where date>'2025-10-15'
    and product_name in ('C++','Java','Python')
    and status='completed'
    group by user_id 
    having count(user_id)>=2
)
and product_name in ('C++','Java','Python')
and status='completed'
and date>'2025-10-15'
) t
where t.r_no<=2 and t.cnt>=2
group by t.user_id
order by t.user_id

思路：目前题目里写的行数最多的代码，中间套用79#牛客的课程订单分析(三)# 的代码，然后对日期和个人次数用了窗口函数，然后作为一个子查询嵌套到外部查询中，在外部查询的where条件中，选取的日期为前俩天，所以t.r_no<=2，既然要选俩个日期，所以至少需要俩行记录，即t.cnt>=2，然后分组排序就好。