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

WITH total AS (
SELECT 
uid,
MAX(DATE(out_time)) AS dt,
DATE(MIN(in_time)) AS reg_date

FROM tb_user_log

GROUP BY uid)

SELECT
user_grade,
ROUND(COUNT(*)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log), 2) AS radio

FROM(
    SELECT
    *,
    (SELECT DATE(MAX(in_time)) FROM tb_user_log) AS today,
    CASE WHEN DATEDIFF((SELECT today), dt) < 7 AND reg_date <> (SELECT today) AND DATEDIFF((SELECT today), reg_date) >= 7 THEN '忠实用户'
         WHEN DATEDIFF((SELECT today), reg_date) < 7 THEN '新晋用户'
         WHEN DATEDIFF((SELECT today), dt) >= 30 THEN '流失用户'
         ELSE '沉睡用户'
	      
    END AS user_grade

    FROM total) AS a 

GROUP BY user_grade

ORDER BY radio DESC