题解 | #统计活跃间隔对用户分级结果#

• 主要思路：
  1. 参考前面几道题的思路，先建立用户活跃表（临时表，使用with...as (...)语句），记录每个用户的活跃时间。
  2. 分别计算出每个用户的最早登陆时间、最近登陆时间以及总数据中的最近登陆时间。
  3. 等级划分顺序，使用case when： (1) 先筛选出流失用户（与最近登陆时间间隔大于29天）； (2) 再继续筛选出沉睡用户（与最近登陆时间间隔大于6天）；(3) 进而筛选出新晋用户（与最早登陆间隔不小于7天）； (4) 最后筛选出忠实用户。
  4. 分组汇总计算出各用户等级的数量以及总用户数量，再计算出比例，保留小数，排序。

with active_user_tb as (
    select uid, date(in_time) as dt
    from tb_user_log
    union all
    select uid, date(out_time) as dt
    from tb_user_log
)

select grade                               as usr_grade,
       round(count(uid) / max(usr_cnt), 2) as ratio
from (
         select uid,
                case
                    when datediff(recent, last_Date) > 29 then '流失用户'
                    when datediff(recent, last_date) > 6 then '沉睡用户'
                    when datediff(recent, first_date) <= 6 then '新晋用户'
                    else '忠实用户' end as grade,
                count(uid) over ()  as usr_cnt
         from (
                  select uid,
                         min(dt) as first_date,
                         max(dt) as last_date
                  from active_user_tb
                  group by uid
              ) as t
                  join (select max(dt) as recent
                        from active_user_tb) as t2
     ) as u
group by grade
order by ratio desc;