题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
select
user_grade,
round(count(uid)/max(user_cnt),2) as ratio
from
(
select
uid,
user_cnt,
case "忠实用户" when frist_dt < 7_ago and last_dt between 7_ago and nt
case "新晋用户" when frist_dt between 7_ago and nt
case "沉睡用户" when last_dt between 30_ago and 7_ago
case "流失用户" when last_dt < 30_ago else null end as user_grade
from
(
select
uid,
nt,
user_cnt,
frist_dt,
last_dt,
date_sub(nt, interval 6 day) as 7_ago, # 七天时间点
date_sub(nt, interval 29 day) as 30_ago # 一个月时间点
from
(
(
(
select
uid,
date(max(out_time)) as last_dt
from tb_user_log
group by uid
) as aa
# 每个用户的最后一次
left join
(
select
date(max(out_time)) as nt,
COUNT(DISTINCT uid) as user_cnt
from tb_user_log
) as bb
# 最近时间
on 1
) as tb_4 left join
(
select
uid,
date(min(in_time)) as frist_dt
from tb_user_log
group by uid
# 每个用户的第一次
) as b_ using(uid)
) as tb_1
) as tb_2
) as tb_3
group by user_grade
order by ratio desc
投递美团等公司7个岗位