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

WITH T2 AS 
(
    SELECT 
            user_id
            ,first_buy_date
            -- ,CASE WHEN T.RN = 2 THEN first_buy_date END second_buy_date
            ,CNT
    FROM (
        SELECT 
            user_id
            ,date first_buy_date
            ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) RN
            ,COUNT(id) OVER (PARTITION BY user_id) CNT
        FROM order_info 
        WHERE DATEDIFF(date,'2025-10-15') > 0
            AND status = 'completed'
            AND product_name IN ('C++','Python','Java')
    ) T 
    WHERE T.RN IN (1,2)
        AND T.CNT >= 2
    ORDER BY user_id
)
SELECT
    user_id
    ,MIN(first_buy_date) first_buy_date
    ,MAX(first_buy_date) second_buy_date
    ,CNT
FROM T2
GROUP BY T2.user_id,T2.CNT
;