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

SELECT
a.user_id,
b.first_buy_date,
c.second_buy_date,
a.cnt
FROM
(
SELECT
user_id,COUNT(1) AS cnt
FROM
order_info 
WHERE
date > '2025-10-15'
AND
status='completed'
AND
product_name IN('C++','Java','Python')
GROUP BY
user_id
HAVING
COUNT(1) >= 2
)a,
(
SELECT
min(date) AS first_buy_date,
user_id
FROM
order_info
WHERE
date > '2025-10-15'
AND
status='completed'
AND
product_name IN('C++','Java','Python')
GROUP BY
user_id
)b,
-- 在上面条件下第二天购买
(
SELECT a.user_id,MIN(a.date) AS second_buy_date
FROM
(
SELECT
user_id,date
FROM
order_info
WHERE
date > '2025-10-15'
AND
status='completed'
AND
product_name IN('C++','Java','Python')
)a
WHERE
NOT EXISTS (SELECT * FROM(
SELECT
user_id,min(date) AS first_buy_date
FROM
order_info
WHERE
date > '2025-10-15'
AND
status='completed'
AND
product_name IN('C++','Java','Python')
GROUP BY
user_id
)b WHERE a.user_id = b.user_id AND a.date = b.first_buy_date)
GROUP BY
a.user_id
)c
WHERE
a.user_id = b.user_id
AND
a.user_id = c.user_id
ORDER BY
a.user_id