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

select user_id ,max(f) ,max(s) ,max(r) from (select if(r=1,date,0) f ,if(r=2,date,0) s ,user_id, date,c,r from (select user_id, date,count(id)over(partition by user_id) c ,rank()over(partition by user_id order by date) r from order_info where date>'2025-10-15' and product_name in('C++','Java','Python') and status='completed' ) a ) b where user_id in (select user_id from (select user_id,min(date) date,count(1) c from order_info where date>'2025-10-15' and product_name in('C++','Java','Python') and status='completed' group by user_id) a where c>=2 ) group by user_id order by user_id