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

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;