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

思路： 求second_buy_date的核心重点是：min(date > min(date))

我们使用订单分析四中的结果，求出最小的date，然后联立 order_info 表，通过 t1.date > t2.first_buy_date 及 min(t1.date) 求出第二小date

完整代码：

select t1.user_id, t2.first_buy_date, min(t1.date) as second_buy_date, t2.cnt
from order_info t1, (select user_id, min(date) as first_buy_date, count(user_id) as cnt
											from order_info
											where status = 'completed'
											and date > '2025-10-15'
											and product_name in ('C++', 'Java', 'Python')
											group by user_id
											having count(user_id) > 1) as t2
where t1.user_id = t2.user_id
and t1.date > t2.first_buy_date
and t1.status = 'completed'
and t1.date > '2025-10-15'
and t1.product_name in ('C++', 'Java', 'Python')
group by user_id
order by user_id