题解 | #牛客的课程订单分析(四)#
牛客的课程订单分析(四)
http://www.nowcoder.com/practice/c93d2079282f4943a3771ca6fd081c23
/建立两个表，一个用来算每个user_id的个数，一个用来求min(date)，再进行左连接/
SELECT t1.user_id,t2.first_buy_date,t1.cnt
FROM
(
SELECT user_id,COUNT(*) AS cnt
from order_info
WHERE date>'2025-10-15'
and product_name IN ('C++','Java','Python')
and status='completed'
GROUP BY user_id
HAVING cnt>=2
)t1
LEFT JOIN
(
SELECT user_id,min(date) AS first_buy_date
from order_info
WHERE date>'2025-10-15'
and product_name IN ('C++','Java','Python')
and status='completed'
GROUP BY user_id
)t2
on t1.user_id=t2.user_id
order by t1.user_id;
