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

WITH t1 AS (
    -- 第一步，先筛选出日期范围内的数据，并且购买状态是成功
	SELECT
    	user_id,
    	product_name,
    	`date`
    FROM order_info
    WHERE `date` > '2025-10-15'
    AND status = 'completed'
    AND product_name IN ('C++','Python','Java')
),
t2 AS (
	-- 第二步，在再求出用户下单次数 >= 2
    SELECT
    	user_id,
        count(user_id) AS cnt
    FROM t1
    GROUP BY user_id
    HAVING count(user_id) >= 2
),
t3 AS (
	-- 第三步，在第一步基础上筛选第二步数据并且进行row_number
    SELECT
    	user_id,
    	product_name,
    	`date`,
    	ROW_NUMBER()
    		OVER(PARTITION BY user_id ORDER BY `date` ASC
            ) AS ranking
    FROM t1
    WHERE user_id IN (SELECT user_id FROM t2)
),
t4 AS (
	-- 第四步，找出左半表，用户id及第一次购买时间
    SELECT
    	user_id,
    	`date` AS first_buy_date
    FROM t3
    WHERE ranking = 1
),
t5 AS (
	-- 第五步，找出中半表，用户id及第二次购买时间
    SELECT
    	user_id,
    	`date` AS second_buy_date
    FROM t3
    WHERE ranking = 2
),
t6 AS (
	-- 第六步，进行数据表关联
    SELECT
    	t4.user_id,
    	t4.first_buy_date,
    	t5.second_buy_date,
    	t2.cnt
    FROM t4
    LEFT JOIN t5
    ON t4.user_id = t5.user_id
    LEFT JOIN t2
    ON t4.user_id = t2.user_id
    ORDER BY t4.user_id ASC
)
SELECT * FROM t6;