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

select r1.user_id, r1.first_buy_date, r2.date, r1.cnt from 
(
select user_id, min(date) first_buy_date, count(*) cnt from 
(select user_id, date, rank() over(partition by user_id order by date) r from order_info
where user_id in
(select user_id from order_info
where date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(*)>=2)
and date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
order by user_id, date
 ) re 
group by user_id
order by user_id
    ) r1
    join
    (select user_id, date, rank() over(partition by user_id order by date) r from order_info
where user_id in
(select user_id from order_info
where date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
group by user_id
having count(*)>=2)
and date>'2025-10-15' and status = 'completed' and product_name in ('C++', 'Java', 'Python')
order by user_id, date
 ) r2 
 on r1.user_id=r2.user_id
 where r2.r=2