SQL第1次和第2次购买的日期

表：order_info
字段：order_id user_id create_date
表：order_detail
字段：order_id sku_id create_date sku_num
表：sku_info
字段：sku_id name

需求： 一个用户成功下单>=2次的手机订单（xiaomi 10 , apple 12 ，小米 13） 那么输出 第一次和第二次成功购买的日期，以及购买手机成功次数

-- 方法一
select 
 user_id
 ,if(num = 1,create_date,'9999-01-01') start_date
 ,if(num = 2,create_date,'9999-01-01') end_date
 ,sum(order_ct) over(partition by user_id) sorder
 
 from (
    select 
     user_id,create_date,order_ct,row_number() over(partition by user_id order by create_date) num 
     from (
        select  user_id,create_date,count(t1.order_id) order_ct from order_info t1 
         join order_detail t2 on t1.order_id = t2.order_id
         join sku_info t3 on t2.sku_id = t3.sku_id
        where t3.name in ('xiaomi 10','apple 12','小米 13')
        group by user_id,create_date 
    ) a 
) b where sorder >= 2


-- 方法二 

select 
 user_id
 ,create_date start_date
 ,nvl(afdate,create_date) end_date
 ,cn 
 
 from (
    select 
     user_id,create_date,order_ct
     -- 第一个
     ,row_number() over(partition by user_id order by create_date) num 
     -- 第二个
     ,lead(create_date,1,null) over(partition by user_id order by create_date) afdate
     ,sum(order_ct) over(partition by user_id) cn 
     from (
        select  user_id,create_date,count(t1.order_id) order_ct from order_info t1 
         join order_detail t2 on t1.order_id = t2.order_id
         join sku_info t3 on t2.sku_id = t3.sku_id
        where t3.name in ('xiaomi 10','apple 12','小米 13')
        group by user_id,create_date 
    ) a 
) b where num = 1 and cn >= 2