Hive综合案例练习（中级）第三十七题：统计活跃间隔对用户分级结果

统计活跃间隔对用户分级结果

题目需求

用户等级：

忠实用户：近7天活跃且非新用户

新晋用户：近7天新增

沉睡用户：近7天未活跃但是在7天前活跃

流失用户：近30天未活跃但是在30天前活跃

假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数

结果如下：

Level（用户等级）	Cn（用户数量)
忠实用户	6
新增用户	3
沉睡用户	1

代码实现

select
  t2.level,
  count(*)
from
  (
    select
      uld.user_id,
      case
         when (date_format(max(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 30))
             then '流失用户'-- 最近登录时间三十天前
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <=date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '忠实用户' -- 最早登陆时间是七天前,并且最近七天登录过
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') >=date_sub(today, 7))
             then '新增用户' -- 最早登录时间是七天内
         when (date_format(min(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7) and date_format(max(uld.login_ts),'yyyy-MM-dd') <= date_sub(today, 7))
             then '沉睡用户'-- 最早登陆时间是七天前,最大登录时间也是七天前
       end level
    from
      user_login_detail  uld
    join
      (
        select 
          date_format(max(login_ts),'yyyy-MM-dd') today 
        from
          user_login_detail
    )t1
    on
      1=1
    group by 
      uld.user_id,t1.today
    )t2
group by
  t2.level