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

用case when新生成两列
后面随便用个聚合函数聚合起来

select user_id,
    min(first_buy_date),
    min(second_buy_date),
    cnt
from 
    (select 
        user_id,
        (case when d_rank=1 then date end) first_buy_date,
        (case when d_rank=2 then date end) second_buy_date,
         cnt
    from 
        (select user_id,
            date,
            dense_rank() over(partition by user_id order by date ASC) as d_rank,
            count(id) over(partition by user_id) as cnt
        from order_info
        where date>'2025-10-15' and product_name in ('C++','Python', 'Java') and status='completed') tmp
    ) tmp2

group by user_id,cnt having min(second_buy_date) is not null
order by user_id;