题目要求

在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出

1、这个用户的user_id

2、这个用户满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date

3、这个用户满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date

4、这个用户（满足前面条件）购买成功的C++课程或Java课程或Python课程的次数cnt

输出结果按照user_id升序排序

思路

select 
    user_id,
    first_buy_date, # min(date)
    second_buy_date, # 窗口函数 升序 rank=2
    cnt # count(id)
from 符合条件的用户user_id，及他在特定条件下的 order_info
order by user_id asc

正确代码

按照上面的框架，很快写出来了 ，但是语法问题调试了好久，代码一多就容易出错...

select
    distinct a.user_id,
    min(a.date) as first_buy_date,
    b.date as second_buy_date,
    count(distinct a.id) as cnt
from (
    # 满足条件的user_id 及其在特定条件下的 订单信息
    select *
    from order_info
    where user_id in (
        # 特定条件下的订单数>=2的用户user_id
        select
            distinct o.user_id
        from order_info o
            left join 
            (
                # 所有用户及其 特定条件下的订单数
                select 
                    distinct user_id,
                    count(id)over(partition by user_id) as order_num
                from order_info
                where date > '2025-10-15'
                    and status = 'completed'
                    and product_name in ('C++','Java','Python')
            )n on o.user_id = n.user_id
        where n.order_num >= 2
    )
    and date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Java','Python')

)a

left join 
(
    select 
        b1.user_id,
        b1.date
    from (
        # 符合条件的用户user_id 及其特定条件下的order_info,及日期排序
        select *,
            row_number()over(partition by user_id order by date asc) as t_rank
        from order_info
        where user_id in (
            # 特定条件下的订单数>=2的用户user_id
            select
                distinct o1.user_id
            from order_info o1
                left join 
                (
                    # 所有用户及其 特定条件下的订单数
                    select 
                        distinct user_id,
                        count(id)over(partition by user_id) as order_num
                    from order_info
                    where date > '2025-10-15'
                        and status = 'completed'
                        and product_name in ('C++','Java','Python')
                )n1 on o1.user_id = n1.user_id
            where n1.order_num >= 2
        )
        and date > '2025-10-15'
        and status = 'completed'
        and product_name in ('C++','Java','Python')
    )b1
    where b1.t_rank = 2
    
)b
on a.user_id = b.user_id
group by a.user_id,b.date
order by a.user_id asc