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

第一步，用where筛选购买状态、课程等，用窗口函数生成用户下单次数及下单日期排序，即
WITH RankedOrders AS (  
    SELECT  
        user_id,  
        date,  
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) as rn,  
        COUNT(*) OVER (PARTITION BY user_id) AS cnt  
    FROM  
        order_info  
    WHERE  
        date > '2025-10-15'  
        AND status = 'completed'  
        AND product_name IN ('C++', 'Python', 'Java')  
)  第二步，分别筛选满足以上条件的第一天日期和第二天日期，并将两个表进行链接，最后从链接的结果中用select显示first_buys.user_id,  first_buys.date AS first_buy_date,   second_buys.date AS second_buy_date,first_buys.cnt，  
    即
SELECT  
    first_buys.user_id,  
    first_buys.date AS first_buy_date,  
    second_buys.date AS second_buy_date,
    first_buys.cnt
FROM  
    (SELECT user_id, date, cnt FROM RankedOrders WHERE rn = 1 AND cnt >= 2) AS first_buys  
LEFT JOIN  
    (SELECT user_id, date, cnt FROM RankedOrders WHERE rn = 2 AND cnt >= 2) AS second_buys  
ON  
    first_buys.user_id = second_buys.user_id  
ORDER BY  
    first_buys.user_id;