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

窗口函数+max 列转行函数

max(case when 列名的条件 then 返回的列 end) as xxx

select
user_id,
max(case when rk=1 then `date` end) as first_buy_date,
max(case when rk=2 then `date` end) as second_buy_date,
cnt
from
(
select 
user_id,
`date`,
dense_rank() over (partition by user_id order by `date`) as rk,
count(`date`) over(partition by user_id) as cnt
from  order_info
where product_name in ('C++','Python','Java')
and `date`>=date('2025-10-15') and status='completed'
)
A
group by user_id,cnt
having cnt>=2
order by user_id