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

select  t.user_id,min(case when t.rn =1 then t.date end ) as first_buy_date #第一次购买日期  
,max(case when t.rn =2 then t.date end ) as second_buy_date 
#第二次购买日期
,count(1) as cnt  #总购买次数
from (
         select t.id,
           t.user_id,
           t.product_name,
           t.status,
           t.client_id,
           t.date,
           row_number()over(partition by t.user_id order by t.date) rn  #给日期排序
    from(
        select distinct
           t.id,
           t.user_id,
           t.product_name,
           t.status,
           t.client_id,
           t.date 
           from order_info t
           where t.product_name in('Java','Python','C++')
           and t.date >'2025-10-15'
           and t.status='completed'
    )t
    
)t
group by t.user_id
having  ifnull(max(case when t.rn =2 then t.date end ),'')<>'' #剔除只有第一次购买的情况