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

对比牛客的课程订单分析(四)，这道题多了一个second_buy_date字段，想到用偏移函数，parition by user_id用户分组，order by date,对日期升序排序，默认偏移一个单位刚好是第二次购买。

SELECT
    user_id
    ,min( date ) AS first_buy_date
    ,second_buy_date
    ,cnt
FROM
    (
    SELECT
        *,
        count( * ) over ( PARTITION BY user_id ) AS cnt,
        lead(date) over(partition by user_id order by date) as second_buy_date
    FROM
        order_info 
    WHERE
        date > '2025-10-15'
        AND `status` = 'completed' 
        AND product_name IN ( 'C++', 'Python', 'Java' ) 
    ) e 
WHERE
    cnt >= 2 
GROUP BY
    user_id
order by 
    user_id;