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

select t3.user_id,t3.first_buy_date,t4.second_buy_date,t3.cnt
from(
		select user_id,`date` first_buy_date,cnt
		from(
				select user_id,`date`,product_name,
				dense_rank() over(partition by user_id order by `date`) rk,
				count(1) over(partition by user_id) cnt
				from order_info
				where date > '2025-10-15'
				and product_name in ('C++','Java','Python')
				and status = 'completed'
		) t1
		where cnt >= 2
		and rk = 1
) t3 join (
	select user_id,`date` second_buy_date,cnt
	from(
			select user_id,`date`,product_name,
			dense_rank() over(partition by user_id order by `date`) rk,
			count(1) over(partition by user_id) cnt
			from order_info
			where date > '2025-10-15'
			and product_name in ('C++','Java','Python')
			and status = 'completed'
	) t2
	where cnt >= 2
	and rk = 2
) t4 on t3.user_id = t4.user_id
order by user_id;