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

SELECT 
    order_info_ext.user_id AS user_id, 
    min(first_buy_date) AS first_buy_date,
    min(date) AS second_buy_date, 
    min(cnt) AS cnt
FROM 
(
    SELECT 
        *, 
        COUNT(*) OVER(PARTITION BY user_id) AS cnt,
        min(date) OVER(PARTITION BY user_id) AS first_buy_date,
        row_number() over(PARTITION BY user_id ORDER BY date ASC) AS buy_date_rk 
    FROM 
        order_info 
    WHERE 
        date > '2025-10-15' 
    AND 
        product_name IN ('C++', 'Java', 'Python') 
    AND 
        status = 'completed' 
) AS order_info_ext
WHERE 
    order_info_ext.cnt >= 2
AND 
    order_info_ext.buy_date_rk = 2 
GROUP BY 
    order_info_ext.user_id