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

with a as (
select distinct uid,date(in_time) dt ,
dense_rank() over(partition by uid order by date(in_time)) rank1
from tb_user_log
),
c as (
select * from (select '忠实用户' as user_grade,count(distinct uid) cnt from a where datediff((select max(dt) from a),dt)<=6 and rank1!=1
union
select '新晋用户' as user_grade,count(distinct uid) cnt from a where datediff((select max(dt) from a),dt)<=6 and rank1=1
)a
union
select * from (
select '沉睡用户' as user_grade,count(distinct uid) cnt from a where datediff((select max(dt) from a),dt) between 7 and 29 and uid not in
(select uid from a where datediff((select max(dt) from a),dt)<=6)
UNION
select '流失用户' as user_grade,count(distinct uid) cnt from a where uid not in
(select uid from a where datediff((select max(dt) from a),dt)<=29)
)b
)
select user_grade,round(cnt/(select sum(cnt) from c),2) as ratio from c
# group by user_grade