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

WITH t1 AS(
SELECT
*,
COUNT(user_id) OVER(PARTITION BY user_id) AS cnt,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY date) AS rk

FROM order_info

WHERE 
status = 'completed' AND
date > '2025-10-15' AND
product_name IN ('C++','Java','Python'))

SELECT
*

FROM(
    SELECT DISTINCT
    user_id,
    date AS first_buy_date
    
    FROM t1
    
    WHERE rk = '1') AS t2
    
JOIN(
    SELECT DISTINCT
    user_id,
    date AS second_buy_date,
    cnt
    
    FROM t1
    
    WHERE rk = '2') AS t3
    
    USING(user_id)
    
ORDER BY user_id