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

with temp0 as (
    select user_id,count(*) as cnt
    from order_info oi
    where date >= "2025-10-15" and product_name in ("C++","Python","Java") and status = "completed" 
    group by user_id
    having count(*) >= 2
), temp1 as (
    select t0.user_id,cnt,date,row_number()over(partition by t0.user_id order by date asc) as rk
    from temp0 t0 inner join order_info oi
    on t0.user_id = oi.user_id
    where date >= "2025-10-15" and product_name in ("C++","Python","Java") and status = "completed"
), temp2 as (
    select distinct user_id,
        (select date from temp1 t2 where t1.user_id = t2.user_id and t2.rk = 1 limit 1) as first_buy_date,
        (select date from temp1 t2 where t1.user_id = t2.user_id and t2.rk = 2 limit 1) as second_buy_date,
    cnt
    from temp1 t1 
    where rk <= 2
    order by user_id asc
)

select * from temp2;