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

#需求：统计活跃间隔对用户分级后，各活跃等级用户占比
#输出：用户等级、占比
#要求：占比数据保留两位小数，降序输出
#特殊点：假设数据中所有日期的最大值为今天
#用户分级：流失和沉睡用户以最晚活跃时间与今天之差为标的，新晋用户以最早活跃时间与今天之差为标的，排除这三种后，剩余的用户即是忠实用户；
#流失用户：活跃间隔在30天之外，沉睡用户：活跃间隔在近7天外30天内，新晋用户：活跃间隔在近7天内，其余为忠实用户
#各等级占比：得到用户分级后，以各级的uid数除以总用户数
#思路：先得到三个日期，‘今天’、各个用户的最近活跃日期、最早活跃日期，case when，新晋用户的判断要以用户首次活跃日期与‘今天’比较在七天内，其他的用户分级则用最近活跃日期判断；
#然后要一个总用户数，各级uid数/总uid数，group by分级，就是各级的占比
with e1 as(
  select w4,timestampdiff(day,w1,w3) w5,timestampdiff(day,w2,w3) w6
  from(
    select min(date(in_time)) w1,max(date(out_time)) w2
    from tb_user_log
    group by uid
  ) e2
  #建表得到每个uid的最早活跃时间w1和最晚活跃时间w2
  left join(
    select max(date(out_time)) w3,count(distinct uid) w4
    from tb_user_log
  ) e3
  #建表得到当前日期w3和用户总数w4
  on 1
  #left join on 1 是一种SQL查询语法。它表示使用左连接将两个表连接起来，并且使用数字1作为连接条件（on 1），这意味着任何非空的行都会被连接，而不需要指定实际的连接条件
  #将建立的两表相连接，用于计算每个用户最早活跃时间与当前日期之差和最晚活跃时间与当前日期之差
)
select user_grade,round(count(user_grade)/max(w4),2) ratio
#子查询中的w4查询会返回多个值，取max才是总的用户数
from(
  select w4,case
  when w6>=30 then "流失用户"
  when w6>=7 then "沉睡用户"
  when w5<7 then "新晋用户"
  else "忠实用户"
  end user_grade
  from e1
) e4
group by user_grade
order by 2 desc