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

# 按照用户ID分组 
  # 最近活跃日期 MAX(DATE_FORMAT(out_time,"%Y-%m-%d")) near_active_dt
  # 最早活跃日期 MIN(DATE_FORMAT(in_time,"%Y-%m-%d")) fitst_active_dt
# 获取当前日期 MAX(DATE_FORMAT(out_time,"%Y-%m-%d")) current_dt
# 计算当前日期和最早活跃、最近活跃日期差TIMESTAMPDIFF(DAY, 时间1，时间2)
# 忠实用户，近7天活跃过且非新晋用户 current_dt-near_active_dt<=6 AND current_dt-fitst_active_dt>6
# 新晋用户,近7天新增 current_dt-fitst_active_dt<=6
# 沉睡用户,近7天未活跃但更早前活跃过 current_dt-near_active_dt BETWEEN 7 AND 29
# 流失用户,近30天未活跃但更早前活跃过 current_dt-near_active_dt> 29 AND current_dt-fitst_active_dt >29
# 统计总用户数COUNT(DISTINCT uid) 并计算占比

SELECT u.user_grade, 
       ROUND(
             COUNT(u.uid) 
             / (SELECT COUNT(DISTINCT uid) FROM tb_user_log) 
            ,2) AS ratio
FROM
    (SELECT g.uid,
        CASE
        WHEN g.near_gap <=6 AND g.first_gap > 6 THEN '忠实用户'
        WHEN g.first_gap <=6 THEN '新晋用户'
        WHEN g.near_gap BETWEEN 7 AND 29 THEN '沉睡用户'
        WHEN g.near_gap >29 AND g.first_gap > 29 THEN '流失用户'
        END AS user_grade
    FROM
        (SELECT t.uid, 
            TIMESTAMPDIFF(DAY, t.near_active_dt, t.current_dt ) AS near_gap,
            TIMESTAMPDIFF(DAY, t.first_active_dt, t.current_dt ) AS first_gap
        FROM
            (SELECT uid, 
                (SELECT MAX(DATE_FORMAT(out_time,"%Y-%m-%d")) FROM tb_user_log) AS current_dt,
                MAX(DATE_FORMAT(out_time, "%Y-%m-%d" )) AS near_active_dt,
                MIN(DATE_FORMAT(in_time, "%Y-%m-%d" )) AS first_active_dt
            FROM tb_user_log
            GROUP BY uid ) AS t
        ) AS g
    ) AS u
GROUP BY u.user_grade
ORDER BY ratio DESC;