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

#user_id+成功的首单日期+成功二单日期+总单数 user_id升序
#2025-10-15后 下单>1 购买C/J/P 成功

select
    f.user_id
    ,first_buy_date
    ,second_buy_date
    ,cnt
from (
        select
            distinct
            user_id
            ,date first_buy_date
            ,cnt
        from (
                select
                    *
                    ,row_number()over(partition by user_id order by date) r#订单排序
                    ,count(user_id)over(partition by user_id) cnt#总单数
                from order_info
                where date>'2025-10-15'#2025-10-15后
                    and product_name in ('C++','Java','Python')#购买C/J/P
                        and status='completed'#成功
                            #2025-10-15后  购买C/J/P 成功 订单排序
                ) a
        where cnt>1#下单>1
            and r=1#首单日期+总单数
        ) f
        join (
                select
                    distinct
                    user_id
                    ,date second_buy_date
                from (
                        select
                            *
                            ,row_number()over(partition by user_id order by date) r#订单排序
                            ,count(user_id)over(partition by user_id) cnt#总单数
                        from order_info
                        where date>'2025-10-15'#2025-10-15后
                            and product_name in ('C++','Java','Python')#购买C/J/P
                                and status='completed'#成功
                                    #2025-10-15后  购买C/J/P 成功 订单排序
                        ) b
                where cnt>1#下单>1
                    and r=2#二单日期
                        ) s
            on s.user_id=f.user_id
order by 1#user_id升序