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

select i.user_id, min(i.date) first_buy_date, b.date second_buy_date, count(*) cnt
from order_info i
inner join (
    select user_id, date, row_number()over(partition by user_id order by date) a
    from order_info
    where datediff(date, '2025-10-15') > 0 and product_name in ('C++', 'Java', 'Python') and status = 'completed' 
    group by user_id, date
) b
on i.user_id = b.user_id
where datediff(i.date, '2025-10-15') > 0 and product_name in ('C++', 'Java', 'Python') and status = 'completed' and b.a = 2
group by i.user_id, second_buy_date
having cnt >= 2
order by i.user_id

因为题目中已经有order_info表了定为i表，i表是代表订单为2个以及以上的表，再定义一个b表，b表代表第二次购买的用户以及日期，通过窗口函数判断第二次的日期并通过select展示出来，最后结合条件可以得到最终结果