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

此题共包含一张表：

表1：tb_user_log

要解决的问题：

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

用户等级标准简化为：

• 忠实用户(近7天活跃过且非新晋用户)、
• 新晋用户(近7天新增)、
• 沉睡用户(近7天未活跃但更早前活跃过)、
• 流失用户(近30天未活跃但更早前活跃过)。
• 假设今天就是数据中所有日期的最大值。
• 近7天表示包含当天T的近7天，即闭区间[T-6, T]。

解题思路：

1. 先计算每一条数据与max(in_time)的时间间隔
2. 根据时间间隔做when case判断，得到各个UID的用户等级
3. 根据用户等级，计算各等级所占比例，且结果保留两位小数
4. 按占比的降序排序

知识点:

1. 在做grade分组时，一定要先获取min(gap) 和 max(dt) 否则获取的结果会有问题

select grade,
	   ROUND(count(grade) / (SELECT count(DISTINCT uid) from tb_user_log),2) ratio
from(
		select uid, 
              case when gap <7 and dt != 1 then '忠实用户'
              when gap < 7 and dt = 1 then '新晋用户'
              when gap >= 7 and gap < 30 then '沉睡用户'
              when gap >= 30 then '流失用户'
              END as grade
		FROM(
            select uid,min(gap) as gap,max(dt) as dt
            from(
              select uid,
              TIMESTAMPDIFF(day,in_time,(SELECT max(in_time) from tb_user_log)) gap,
              count(*) over (PARTITION by uid ORDER BY in_time) dt
              from tb_user_log
            	) gap_table
		GROUP by uid)  uid_name_table
	)info
GROUP by grade
order by ratio desc,grade