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

SELECT a.user_id,a.first_buy_date,(
SELECT COUNT(*)
FROM order_info AS c
WHERE a.user_id=c.user_id AND c.status='completed' AND c.product_name IN ('C++','Java','Python')

) AS cnt
FROM (
SELECT b.user_id,MIN(b.date) AS first_buy_date
FROM order_info AS b
WHERE b.date>'2025-10-15' AND b.status='completed' AND b.product_name IN ('C++','Java','Python')
GROUP BY b.user_id
HAVING COUNT()>=2
) AS a
ORDER BY a.user_id
*/
SELECT b.user_id,MIN(b.date) AS first_buy_date,COUNT() AS cnt
FROM order_info AS b
WHERE b.date>'2025-10-15' AND b.status='completed' AND b.product_name IN ('C++','Java','Python')
GROUP BY b.user_id
HAVING COUNT(*)>=2
ORDER BY b.user_id