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

第一步通过窗口函数生成日期升序的字段rn和用户买课次数的字段 cnt，其余条件直接用where筛选，即 (
    SELECT 
        user_id, 
        date,
        COUNT(*) OVER (PARTITION BY user_id) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn
    FROM 
        order_info
    WHERE 
        date > '2025-10-15'
        AND status = 'completed'
        AND product_name IN ('C++', 'Python', 'Java')
) as RankedOrders
第二步在此基础上通过case when函数筛选出日期排名第一和第二的结果并分别命名first_buy_date，second_buy_date，且用where函数选出2次以上买课并最后用user_id排序，即
SELECT 
    user_id, 
    MIN(CASE WHEN rn = 1 THEN date END) AS first_buy_date,
    MIN(CASE WHEN rn = 2 THEN date END) AS second_buy_date,
    cnt
FROM (
    SELECT 
        user_id, 
        date,
        COUNT(*) OVER (PARTITION BY user_id) AS cnt,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rn
    FROM 
        order_info
    WHERE 
        date > '2025-10-15'
        AND status = 'completed'
        AND product_name IN ('C++', 'Python', 'Java')
) as RankedOrders
WHERE 
    cnt >= 2
GROUP BY 
    user_id;