只要理解清楚题目的含义，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;