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

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

http://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af

只要理解清楚题目的含义，11行代码就能搞定啦~

一、题目理解

把用户分成4个等级，并分别统计各个等级的用户占总用户的比，结果按照占比降序排列。4个等级的划分规则如下：

忠实用户(近7天活跃过且非新晋用户)
新晋用户(近7天新增)
沉睡用户(近7天未活跃但更早前活跃过)
流失用户(近30天未活跃但更早前活跃过)。

今天就是数据中所有日期的最大值，近7天表示包含当天T的近7天，即闭区间[T-6, T]。

审题后发现有几个关键日期非常重要：

日期1—最后一次活跃日期：MAX(DATE(out_time)) AS max_dt
日期2—成为新用户的日期：MIN(DATE(in_time)) AS new_dt
日期3—今天：MIN(DATE(in_time)) AS new_dt

二、解题

整体的思路是：先把核心日期取出，做一张用户活跃信息表（包含用户id，最后一次活跃日期，成为新用户日期，以及“今天”日期），之后再根据等级划分的条件取定义用户分层，最后统计分层用户占比数据。

1）建立用户活跃信息表

最后一次活跃日期：MAX(DATE(out_time)) AS max_dt

成为新用户的日期：MIN(DATE(in_time)) AS new_dt

今天：MIN(DATE(in_time)) AS new_dt

SELECT uid, MAX(DATE(out_time)) max_dt,MIN(DATE(in_time)) min_dt,MAX(MAX(DATE(out_time)))OVER() now_dt
FROM tb_user_log GROUP BY uid;

2）定义各个分层用户

DATEDIFF(now_dt,new_dt)<=6 THEN '新晋用户' #定义近7天新增用户为'新晋用户'
DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 THEN'忠实用户' #近7天活跃过且非新晋用户
DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 THEN '沉睡用户' #近（8-30）天内活跃过且非新晋用户
ELSE '流失用户' END user_grade #剩下的就是流失用户了

SELECT 
	CASE WHEN DATEDIFF(now_dt,new_dt)<=6 THEN '新晋用户'  #近7天新增
	      WHEN DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 THEN'忠实用户' #近7天活跃过且非新晋用户
          WHEN DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 THEN '沉睡用户'
	      ELSE '流失用户' END user_grade
FROM (
	SELECT uid, MAX(DATE(out_time)) max_dt,MIN(DATE(in_time)) new_dt,MAX(MAX(DATE(out_time)))OVER() now_dt
	FROM tb_user_log GROUP BY uid) act_table

3）统计各个层级用户，保留2为小数，并按照ratio降序排列。

用 SUM(COUNT(user_grade))OVER()来统计总用户数。

WITH t1 AS(
SELECT 
		CASE WHEN DATEDIFF(now_dt,new_dt)<=6 THEN '新晋用户'  #近7天新增
		      WHEN DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 THEN'忠实用户' #近7天活跃过且非新晋用户
              WHEN DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 THEN '沉睡用户'
		      ELSE '流失用户' END user_grade
FROM (
	SELECT uid, MAX(DATE(out_time)) max_dt,MIN(DATE(in_time)) new_dt,MAX(MAX(DATE(out_time)))OVER() now_dt
	FROM tb_user_log GROUP BY uid) act_table
)-- t1表  
SELECT user_grade,ROUND(COUNT(user_grade)/SUM(COUNT(user_grade))OVER(),2)ratio
FROM t1 GROUP BY user_grade ORDER BY ratio DESC;