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

WITH t1 AS(

SELECT DISTINCT uid,

    MAX(DATE(in_time)) OVER(PARTITION BY uid) AS act,

    MAX(DATE(in_time)) OVER() AS to_day,

    MIN(DATE(in_time)) OVER (PARTITION BY uid) AS log_time

from tb_user_log)

,t2 AS(

SELECT uid,

    CASE

        WHEN DATEDIFF(to_day,log_time)<7 THEN '新晋用户'

        WHEN DATEDIFF(to_day,act)<7 THEN '忠实用户'

        WHEN DATEDIFF(to_day,act)>=30 THEN '流失用户'

        ELSE '沉睡用户'

        END AS user_grade

FROM t1)

SELECT user_grade,

    ROUND(COUNT(*)/(select count(*) from t2),2) AS ratio

FROM t2

GROUP BY user_grade

ORDER BY ratio DESC,user_grade