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

WITH filtered_orders AS (
    SELECT 
        user_id,
        date,
        product_name,
        COUNT(*) OVER (PARTITION BY user_id) AS cnt
    FROM order_info
    WHERE 
        date > '2025-10-15'
        AND product_name IN ('C++', 'Python', 'Java')
        AND status = 'completed'
),
eligible_users AS (
    SELECT DISTINCT user_id
    FROM filtered_orders
    WHERE cnt >= 2
),
first_and_second_buy AS (
    SELECT 
        o1.user_id,
        MIN(o1.date) AS first_buy_date,
        (
            SELECT MIN(o2.date) 
            FROM filtered_orders o2 
            WHERE o2.user_id = o1.user_id AND o2.date > MIN(o1.date)
        ) AS second_buy_date,
        COUNT(*) AS cnt
    FROM filtered_orders o1
    JOIN eligible_users e ON o1.user_id = e.user_id
    GROUP BY o1.user_id
)
SELECT 
    user_id, 
    first_buy_date, 
    second_buy_date, 
    cnt
FROM first_and_second_buy
ORDER BY user_id ASC;