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

SELECT * FROM
	(SELECT 
		user_grade,
	 -- 因为这里有用GROUPBY子句,所以这里一定要对users聚合,但因为users的值是一样的,MAX/AVG/MIN都一样
		ROUND(COUNT(user_grade) / MAX(users), 2) AS ratio
	FROM
		(SELECT 
			uid,
			users,
		 -- 若最近的时间间隔都已超过30天，则是流失用户，若最近的时间间隔大于7天，小于30天，则是沉睡用户；
		 -- 若最远的时间间隔在7天之内,则是新进用户,忠实用户的情况最难判断,因此直接用ELSE进行判断
			CASE WHEN interval_s >= 30 THEN '流失用户'
				WHEN interval_s >= 7 THEN '沉睡用户'
				WHEN interval_l < 7 THEN '新晋用户'
				ELSE '忠实用户'
			END AS user_grade
		FROM
			(SELECT 
				uid,
				users,
				DATEDIFF(recent, first) AS interval_l,
				DATEDIFF(recent, last) AS interval_s
			FROM
			 -- 首先找出每位用户最早和最晚登录时间
			(SELECT 
				uid,
				DATE(MIN(in_time)) AS first,
				DATE(MAX(out_time)) AS last
			FROM tb_user_log
			GROUP BY uid) AS t1
			 -- 找到最近时间,以便以后计算间隔时间
			LEFT JOIN
				(SELECT 
					DATE(MAX(out_time)) AS recent,
					COUNT(DISTINCT uid) AS users
				FROM tb_user_log) AS t2 ON 1) AS t3) AS t4
	GROUP BY user_grade) AS t5
ORDER BY ratio DESC;