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

select 
    user_id,
    max(CASE WHEN  rn_001 = 1 THEN date ELSE 0 END) AS first_buy_date,
    max(CASE WHEN  rn_001 = 2 THEN date ELSE 0 END) AS second_buy_date,
    count(product_name)
from(
select a.*,ROW_NUMBER() OVER (PARTITION BY user_id  ORDER BY date ) rn_001
from(
select a.*,count(product_name) OVER (PARTITION BY user_id)num
from order_info a
where date >'2025-10-15'
and product_name in('Java','Python','C++')
and status ='completed')a
where num>=2)b
group by user_id

利用窗口函数计算排名 并用case when 进行排名筛选