昨天开始规范格式，于是...| #牛客的课程订单分析(五)#

只能说从昨天开始强迫自己增加代码可读性之后，写出来的SQL突然就美丽了许多。。

WITH ranked AS (
    SELECT 
        *, 
        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 = 'Python' OR product_name = 'C++' OR product_name = 'Java')
), 

first AS (
    SELECT 
        user_id, 
        date AS first_buy_date, 
        cnt
    FROM 
        ranked
    WHERE 
        rn = 1 AND cnt >= 2
    ORDER BY 
        user_id
), 

second AS (
    SELECT
        user_id, 
        date AS second_buy_date
    FROM 
        ranked
    WHERE 
        rn = 2 AND cnt >= 2 
    ORDER BY 
        user_id
)

SELECT
    f.user_id, 
    first_buy_date, 
    second_buy_date,
    cnt
FROM
    first f 
JOIN 
    second s 
ON 
    f.user_id = s.user_id