题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
select
user_grade,
round(count(user_grade)/(select count(*) from (select
uid,
case when dt is not null and first_id is not null and dt != first_id then '忠实用户'
when dt = first_id then '新晋用户'
when dt is null and datediff((select max(date(in_time)) from tb_user_log),first_id) >=7
and datediff((select max(date(in_time)) from tb_user_log),first_id) <30 then '沉睡用户'
else '流失用户' end 'user_grade'
from
(select distinct
b.uid,
dt,
first_id
from
(select uid,date(in_time) dt from tb_user_log
where datediff((select max(date(in_time)) from tb_user_log),date(in_time)) <=6
order by dt) a
right join
(select uid,min(date(in_time)) first_id from tb_user_log group by uid) b
on a.uid = b.uid) c) e),2) ratio
from
(select
uid,
case when dt is not null and first_id is not null and dt != first_id then '忠实用户'
when dt = first_id then '新晋用户'
when dt is null and datediff((select max(date(in_time)) from tb_user_log),first_id) >=7
and datediff((select max(date(in_time)) from tb_user_log),first_id) <30 then '沉睡用户'
else '流失用户' end 'user_grade'
from
(select distinct
b.uid,
dt,
first_id
from
(select uid,date(in_time) dt from tb_user_log
where datediff((select max(date(in_time)) from tb_user_log),date(in_time)) <=6
order by dt) a
right join
(select uid,min(date(in_time)) first_id from tb_user_log group by uid) b
on a.uid = b.uid) c) d
group by user_grade
order by ratio desc;
阿里云工作强度 731人发布