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

with cte as (
select uid,min(login_date)       as reg_date,     #注册时间
           max(login_date)       as rec_date,     #最近活跃时间
           max(max(login_date))over() as cur_date      #现在时间
from user_login_tb
group by uid    ),
cte1 as (
select uid,login_date as sec_date,                #次活跃时间
           row_number()over(partition by uid order by login_date desc) as rn
from user_login_tb ),
cte2 as (
select a.uid,reg_date,rec_date,cur_date,sec_date
from cte a left join cte1 b on a.uid=b.uid and b.rn=2 ),
cte3 as (
select uid, case when datediff(cur_date,reg_date)<=6  then '新增用户'
                 when datediff(cur_date,rec_date)>=30 then '流失用户'
                 when datediff(cur_date,rec_date)>6   then '沉默用户'
                 when rec_date=cur_date and datediff(cur_date,sec_date)>=30 then '回流用户'
            else '忠实用户' end as user_grade
from cte2 )
select user_grade,count(*) as num,
                  round(count(*)/sum(count(*))over(),2) as ratio
from cte3
group by user_grade
order by ratio desc

逻辑上感觉不是很严谨，不知道什么样的公司会这样给客户分群