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

#比较好理解的解法
with tiaojian as (
select
uid,
min(date(in_time))over(partition by uid order by date(in_time)) as first_date,
max(date(in_time))over(partition by uid order by date(in_time)) as max_date,
lag(date(in_time),1)over(partition by uid order by date(in_time) asc) as last_date,
max(date(in_time))over() as now_date,
row_number()over(partition by uid order by date(in_time) desc) as m 
from tb_user_log
)

select 
user_grade,
round(
count(uid)/pt,2) as ratio
from(
select 
uid,
case 
when datediff(now_date,max_date)<=6 and datediff(now_date,first_date)>6 then "忠实用户"
when  datediff(now_date,first_date)<=6 then "新晋用户"
when  datediff(now_date,max_date) between 7 and 29 then "沉睡用户"
when  datediff(now_date,max_date)>29 then "流失用户" end as user_grade,
count(uid)over()pt
from tiaojian 
where
m=1
) as t 
group by user_grade,pt
order by ratio desc,user_grade