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

SELECT DISTINCT t3.user_id,t1.first_buy_date,t2.second_buy_date,t3.cnt
FROM(SELECT user_id,date as first_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 1
) t1
JOIN (
    SELECT user_id,date as second_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 2
) t2 on t1.user_id = t2.user_id
JOIN (
    SELECT user_id,count(1) cnt
    FROM order_info
    WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
    GROUP BY user_id
    HAVING count(1) > 1
) t3 ON t1.user_id = t3.user_id
ORDER BY t3.user_id

1、找到用户第一次购买日期

SELECT user_id,date as first_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 1

2、找到用户第二次购买日期

 SELECT user_id,date as second_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 2

3、用户购买次数 >= 2

SELECT user_id,count(1) cnt
    FROM order_info
    WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
    GROUP BY user_id
    HAVING count(1) > 1

4、三表连接，完整代码如下

SELECT DISTINCT t3.user_id,t1.first_buy_date,t2.second_buy_date,t3.cnt
FROM(SELECT user_id,date as first_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 1
) t1
JOIN (
    SELECT user_id,date as second_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 2
) t2 on t1.user_id = t2.user_id
JOIN (
    SELECT user_id,count(1) cnt
    FROM order_info
    WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
    GROUP BY user_id
    HAVING count(1) > 1
) t3 ON t1.user_id = t3.user_id
ORDER BY t3.user_id