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

select user_id, f first_buy_date, `date` second_buy_date, cnt
from (
    # 使用窗口函数查询出需求列的子查询
    select user_id, `date`, min(`date`)over(partition by user_id) f,
    dense_rank()over(partition by user_id order by `date`) s, count(*)over(partition by user_id) cnt
    from order_info
    where datediff(`date`, '2025-10-15') > 0
    and status = 'completed' and product_name in ('C++', 'Java', 'Python')
) t1
where cnt > 1
and s = 2
order by user_id;