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

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

t2 as (
select
  user_id,date as second_buy_date
from 
  (
    select user_id,date,dense_rank() over(partition by user_id order by date) as rk from order_info  where date>'2025-10-15' and product_name in ('C++','Python','Java') and status='completed'
  ) as t2
where rk=2)

select t1.user_id,first_buy_date,second_buy_date,cnt 
from t1
join t2
on t1.user_id=t2.user_id
order by user_id