SQL活跃用户等级

表：user_login_detail
字段：user_id login_ts

需求：设置活跃用户分级
忠实用户：近7天活跃且非新用户
新晋用户：近7天新增
沉睡用户：近7天未活跃，但7天前活跃

-- 方法一

select 
 user_id,
 case 
 when first_day between date_sub(date(now()),interval 7 day) and date(now()) then '新晋用户'
 when first_day <  date_sub(date(now()),interval 7 day)  and  last_day between date_sub(date(now()),7) and date(now()) then '忠实用户'
 else '沉睡用户'
 end 用户等级
 
 from (
    select user_id,if(firstd = 1,login_date,null) first_day,if(lastd = 1,login_date,null) last_day
     
     from (
    
        select user_id,login_date,row_nunber() over(partition by user_id order by login_date) firstd,
         row_nunber() over(partition by user_id order by login_date desc) lastd 
         from (
            select distinct user_id,date(login_ts) login_date from user_login_detail
        ) a 
    ) b 
) c 
;


-- 方法二


select 
 user_id
 ,if(min_day >= day_7, '新晋用户',if(max_day >= day_7,'忠实用户','沉睡用户') ) 用户等级
 
 
 from (
    select 
     user_id,login_date,max(login_date) over(partition by login_date) max_day
     ,min(login_date) over(partition by login_date) min_day
     ,today
     ,date_sub(today,7) day_7
     from (
        select user_id,date_format(login_ts,'yyyy-mm-dd') login_date
        , date_format(max(login_ts) over(),'yyyy-mm-dd') today from user_login_detail
    ) a 
)