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;
查看33道真题和解析