最蠢的方法 | #统计活跃间隔对用户分级结果#

WITH a AS(
    SELECT uid, in_time, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time DESC) AS rn, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY in_time) AS rn2 FROM tb_user_log GROUP BY uid, in_time
),

b AS (SELECT uid, in_time FROM a WHERE rn = 1 AND in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FROM tb_user_log), INTERVAL 6 DAY) AND (SELECT MAX(in_time) FROM tb_user_log) AND uid NOT IN (SELECT uid FROM a GROUP BY uid HAVING COUNT(rn) = 1) GROUP BY uid, in_time ), #7天内活跃但非新晋

c AS (SELECT uid, in_time FROM a WHERE rn = 1 AND in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FROM tb_user_log), INTERVAL 6 DAY) AND (SELECT MAX(in_time) FROM tb_user_log) AND uid NOT IN (SELECT uid FROM b)), #7天内新晋

d AS (SELECT uid, in_time FROM a WHERE rn2 = 1 AND in_time BETWEEN DATE_SUB((SELECT MAX(in_time) FROM tb_user_log), INTERVAL 29 DAY) AND DATE_SUB((SELECT MAX(in_time) FROM tb_user_log), INTERVAL 6 DAY) GROUP BY uid, in_time),

e AS (SELECT uid, in_time FROM a WHERE rn = 1 AND in_time < DATE_SUB((SELECT MAX(in_time) FROM tb_user_log), INTERVAL 29 DAY) GROUP BY uid, in_time)

SELECT '忠实用户' AS user_grade, ROUND(COUNT(DISTINCT uid)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) AS ratio FROM b
UNION 
SELECT '新晋用户' AS user_grade, ROUND(COUNT(DISTINCT uid)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) AS ratio FROM c
UNION
SELECT '沉睡用户' AS user_grade, ROUND(COUNT(DISTINCT uid)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) AS ratio FROM d
UNION
SELECT '流失用户' AS user_grade, ROUND(COUNT(DISTINCT uid)/(SELECT COUNT(DISTINCT uid) FROM tb_user_log),2) AS ratio FROM e