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

select t3.user_id,
t3.first_buy_date,
tc.second_buy_date,
t3.cnt from
(select * from (select 
user_id,
case when rn = 1 then date else null end as first_buy_date,
cnt
from 
(select 
*,
dense_rank() over(partition by user_id order by date) as rn,
count(1) over(partition by user_id) as cnt
from order_info where status='completed' and date > '2025-10-15' and product_name in ('C++','Python','Java')) t1
where cnt >=2) t2
where first_buy_date is not null) t3 join
(select * from (select 
user_id,
case when rn = 2 then date else null end as second_buy_date,
cnt
from 
(select 
*,
dense_rank() over(partition by user_id order by date) as rn,
count(1) over(partition by user_id) as cnt
from order_info where status='completed' and date > '2025-10-15' and product_name in ('C++','Python','Java')) ta
where cnt >=2) tb
where second_buy_date is not null) tc
on t3.user_id = tc.user_id
order by t3.user_id;

思路是先把firstdate搞出来，再把seconddate搞出来，然后你把俩表join在一起，order by userid asc 即可。