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

with t as(
    select *,count(*) over(partition by user_id)cnt,
    rank() over(partition by user_id order by date)r
    from order_info
    where date > '2025-10-15' and status = 'completed' 
        and (product_name = 'C++' 
        or product_name = 'Python' or product_name = 'Java')
)
select user_id, min(date),max(date),cnt
from t
where cnt > 1 and r<= 2
group by user_id