题解 | #各用户活跃分层人数统计#

with tiaojian as (
select 
t.id,
t.uid,
t.login_date,
t.zj,
t.zz,
t.now_day,
t.pt
from(
select 
id,uid,
login_date,
max(login_date)over(partition by uid) as  zj,
min(login_date)over(partition by uid) as zz,
max(login_date)over() as now_day,
lead(login_date,1)over(partition by uid order by login_date desc) as pt,
row_number()over(partition by uid order by login_date desc)  as pm
from user_login_tb
)  as t 
where
t.pm=1
),tiaojian1 as (
select
uid,
case when datediff(now_day,zj)>=30 then "流失用户"
     when datediff(now_day,zj) between 7 and 29 then "沉默用户"
     when datediff(now_day,zz)<7 then "新增用户"
     when datediff(now_day,zj)=0 and datediff(now_day,pt)>30 then "回流用户"
     when datediff(now_day,zj)<=6 and datediff(now_day,pt)<=29
     then "忠实用户"
     end as user_grade
from tiaojian
)


select 
user_grade,
count( uid) as num,
round(
count(uid)/(select  count(distinct uid) from tiaojian1),2) as ratio
from tiaojian1
group by user_grade
order by ratio desc