题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
select user_grade,
round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio
from (
### 第二步：根据时间差分级别：最大时间差difff1<7且 只有1次登陆（difff1=difff2），代表只在7天内活跃，是新晋
### 7天内活跃且difff1<>difff2，也就是7天内活跃了，之前也活跃了，是忠实
### 最大difff1 超过30天了，就是流失，其他就是沉睡
select case when difff1<7 and difff1=difff2 then "新晋用户"
when difff1<7 and difff1<>difff2 then "忠实用户"
when difff1>=30 then "流失用户"
else "沉睡用户" end as user_grade
,uid
from (
### 第一步：找到uid的最大、最小时间差：今天到最大的时间差difff1和今天到最小的时间差difff2
select uid,
datediff((select max(in_time) from tb_user_log),max(in_time)) difff1,
datediff((select max(in_time) from tb_user_log),min(in_time)) difff2
from tb_user_log
group by uid
### 第一步结束
) temp
### 第二步结束，剩下就是查一下distinct uid总数，用user_grade分组后查询每个级别有几个uid, 除一下总uid,就是ratio
) temp2
group by user_grade
order by ratio desc
### #
