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

select c.user_id, b.first_buy_date, b.second_buy_date, c.cnt
from 
(select a.user_id,max((case when r=1 then a.date end)) first_buy_date, max((case when r=2 then a.date end)) second_buy_date
    from 
       (select user_id, date, row_number()over(partition by user_id order by date asc) r from order_info
         where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
       ) a
    where a.r<=2
    group by a.user_id
) b  #b表为用户和分别购买的两次日期
join
(select user_id, count(user_id) cnt
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
group by user_id
having count(user_id)>=2
) c #c表为用户和购买次数
on b.user_id=c.user_id

在建立b表时，我选择将日期行转化为列，创建字段时套一个聚合函数，进行对用户的分组