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

select b.user_id,b.first_buy_date,c.second_buy_date,b.cnt from
(select distinct a.user_id,
date as first_buy_date,
num as cnt
from (
    select user_id,count(*) over(partition by user_id ) as num,
    row_number() over(partition by user_id order by date) as date_rk,date
    from order_info
    where date>'2025-10-15'
    and status ='completed'
    and (product_name ='C++' or product_name ='Java' or product_name ='Python')
)a
where a.num>=2
and a.date_rk=1)b
join
(
select distinct a.user_id,
date as second_buy_date,
num as cnt
from (
    select user_id,count(*) over(partition by user_id ) as num,
    row_number() over(partition by user_id order by date) as date_rk,date
    from order_info
    where date>'2025-10-15'
    and status ='completed'
    and (product_name ='C++' or product_name ='Java' or product_name ='Python')
)a
where a.num>=2
and a.date_rk=2)c
on b.user_id =c.user_id
order by b.user_id

看似复杂，但其实是把上一题复制成2个表，1个用来求first date，1个用来求second date