#统计活跃间隔对用户分级结果#开窗函数的妙用

-- 解题思路；
-- 以人为中心，需要给这些人做一下计算：第一次登陆到今天的天数n_days、最近一次登陆到今天的天数l_days
-- 从上面可以做一个漏斗模型，先把n_days<7的用户标记为新晋用户，再分析一个用户l_days< 7，那么该用户是忠实用户，剩下的用户中l_days<30,是沉睡用户，最后剩下的用户就是流失用户

-- 做出每一个人第一次登陆的时间和最近一次登陆的时间

    select t1.uid,
       t1.new_day,
       t2.first_day,
       datediff(max(t1.new_day) over (order by t1.new_day desc),t1.new_day) n_days,
       datediff(max(t1.new_day) over (order by t1.new_day desc),t2.first_day) l_days
from
(select uid,
       max(out_time) new_day
from tb_user_log
group by uid) t1
left join (
    select uid,
           min(out_time) first_day
    from tb_user_log
    group by uid
    ) t2
on t1.uid = t2.uid
;

-- 将两个时间相相等时，说明这个用户只登陆过一次，那也说明是新用户。

with cte001 as (
    select t1.uid,
       t1.last_landing_time,
       t2.registration_time,
       datediff(max(t1.last_landing_time) over (order by t1.last_landing_time desc),t1.last_landing_time) last_landing_days,
       datediff(max(t1.last_landing_time) over (order by t1.last_landing_time desc),t2.registration_time) registration_days
    from
    (select uid,
           max(out_time) last_landing_time
    from tb_user_log
    group by uid) t1
    left join (
        select uid,
               min(out_time) registration_time
        from tb_user_log
        group by uid
        ) t2
    on t1.uid = t2.uid
)
     ,
  cte002 as (
select uid,
       if(last_landing_days = registration_days,
           if(registration_days < 7,'新晋用户',if(registration_days < 30,'沉睡用户','流失用户')),
           if(last_landing_days < 7,'忠实用户',if(last_landing_days < 30,'沉睡用户','流失用户'))) user_grade
from cte001
      ),
  cte003 as (
      select uid ,user_grade,count(uid) over () sum_users
    from cte002
  )
select user_grade, round(count(*)/sum_users,2) ratio
from cte003
group by user_grade,sum_users
order by ratio desc ;