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

WITH t1 AS (
    SELECT *
    FROM (SELECT uid,MIN(DATE(in_time)) AS first_in_time,
           MAX(DATE(out_time)) AS last_out_time
    FROM tb_user_log l1 
    GROUP BY uid ) AS f1 
    LEFT JOIN (
        SELECT MAX(DATE(out_time)) AS current_dt,
               COUNT(DISTINCT uid) AS uid_cnt
        FROM tb_user_log
    ) AS f2 ON 1
),
t2 AS (
    SELECT uid,uid_cnt,
           TIMESTAMPDIFF(DAY,first_in_time,current_dt) AS first_diff,
           TIMESTAMPDIFF(DAY,last_out_time,current_dt) AS 
           last_diff
    FROM t1 
),
t3 AS (
    SELECT uid,uid_cnt,
           CASE WHEN first_diff<7 THEN '新晋用户'
                WHEN last_diff>=7 AND last_diff<30 THEN '沉睡用户'
                WHEN last_diff>=30 THEN '流失用户'
                ELSE '忠实用户'
                END AS use_grade
    FROM t2 
)
SELECT use_grade,ROUND(COUNT(DISTINCT uid)/MAX(uid_cnt),2) AS ratio
FROM t3 
GROUP BY use_grade
ORDER BY ratio DESC