题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
SELECT user_grade, round(count(uid)/(select count(distinct uid) from tb_user_log),2) ratio FROM (SELECT uid, (CASE WHEN DATEDIFF(DATE((SELECT MAX(in_time) FROM tb_user_log)),date(max(in_time)))<=6 AND DATEDIFF(DATE((SELECT MAX(in_time) FROM tb_user_log)),date(min(in_time)))>6 THEN '忠实用户' WHEN DATEDIFF(DATE((SELECT MAX(in_time) FROM tb_user_log)),date(min(in_time)))<=6 THEN '新晋用户' WHEN DATEDIFF(DATE((SELECT MAX(in_time) FROM tb_user_log)),date(max(in_time))) BETWEEN 7 AND 29 THEN '沉睡用户' WHEN DATEDIFF(DATE((SELECT MAX(in_time) FROM tb_user_log)),date(max(in_time)))>29 THEN '流失用户' END) AS user_grade FROM tb_user_log GROUP BY uid) a GROUP BY user_grade ORDER BY ratio DESC;