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

WITH t AS(SELECT
uid,
TIMESTAMPDIFF(DAY,MAX(in_time),(SELECT MAX(in_time) FROM tb_user_log)) AS '最后登录距今天数',
TIMESTAMPDIFF(DAY,MIN(in_time),(SELECT MAX(in_time) FROM tb_user_log)) AS '最早登录距今天数'
FROM tb_user_log
GROUP BY uid)

SELECT
CASE 
    WHEN 最早登录距今天数 < 7 THEN "新晋用户"
    WHEN 最后登录距今天数 >29 THEN "流失用户"
    WHEN 最后登录距今天数 >6 THEN "沉睡用户"
    ELSE "忠实用户"
    END AS user_grade,
ROUND(COUNT(*)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) AS ratio
FROM t
GROUP BY user_grade
ORDER BY ratio DESC