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

# 首先根据定义，今天是out_time的最大值
# 新增一个字段：每个用户的最早登录日期
# 新晋用户：用户的最早登录日期在近七天内 0
# 忠实用户：用户的最早登录日期在七天前，而且近七天内活跃过 1
# 流失用户：用户的最早登录日期在三十天前，但近三十天内没有活跃过 2
# 沉睡用户：用户的最早登录日期在七天前，但近七天内没有活跃过 3

 
WITH temp_0 AS( # 临时表0，查询每条记录的uid，当天日期，今天，每个用户的首次活跃日期
    SELECT uid, DATE_FORMAT(out_time, '%Y-%m-%d') date_time,  # 记录当天日期
        MAX(DATE_FORMAT(out_time, '%Y-%m-%d')) OVER() today,  # 今天
        MIN(DATE_FORMAT(out_time, '%Y-%m-%d')) OVER(PARTITION BY uid) date_u # 每个用户的最早登录日期
    FROM tb_user_log
    ),
    temp_1 AS( # 临时表1， 按用户分组查询，uid，最后活跃日期，今天，最早活跃日期
    SELECT uid, MAX(date_time) last_date, MAX(today) now_date, MAX(date_u) first_date
    FROM temp_0
    GROUP BY uid
    ),
    temp_2 AS( # 临时表2，针对按用户的分组的表，根据题目条件，分别筛选
    SELECT uid, 
        CASE WHEN first_date BETWEEN DATE_SUB(now_date, INTERVAL 6 DAY) AND now_date THEN '新晋用户'
            WHEN last_date BETWEEN DATE_SUB(now_date, INTERVAL 6 DAY) AND now_date THEN '忠实用户'
            WHEN first_date < DATE_SUB(now_date, INTERVAL 29 DAY) AND last_date < DATE_SUB(now_date, INTERVAL 29 DAY) THEN '流失用户'
            WHEN first_date < DATE_SUB(now_date, INTERVAL 6 DAY) AND last_date < DATE_SUB(now_date, INTERVAL 6 DAY) THEN '沉睡用户'
            END user_grade
    FROM temp_1
    ),
    temp_3 AS( # 临时表3，计算每个等级有多少个用户
    SELECT user_grade, COUNT(*) cnt
    FROM temp_2
    GROUP BY user_grade
    )

# 主查询，等级，占比
SELECT user_grade, ROUND(cnt/all_cnt, 2) ratio
FROM( # 子查询，查询等级，每个等级的用户数，开窗函数计算所有用户数
    SELECT user_grade, cnt, SUM(cnt) OVER() all_cnt
    FROM temp_3
    ) a # 别名
ORDER BY ratio DESC, user_grade # 不加个等级名正序发过不了

思路：1.理解用户等级的标准：活跃间隔

2.根据活跃间隔，找到活跃间隔的计算依据，今天，最早活跃日期，最近活跃日期，这里注意要分组取最值，因为要找到一个用户最有代表性的记录，有些用户有多个记录

3.根据计算依据计算不同人的等级，放在一个新字段里

4.根据等级字段分组计算每个等级的人数

5.根据分组结果，开窗计算所有等级的用户数。将之作为子查询，在基础上进行主查询，计算占比

6.排序