题解 | #连续签到领金币#

-- 一直都不是很会计算这类题目,这次参考大佬的解答写一个回复简单记录下
WITH t1 AS
-- 第一张子表：只保留我们需要的记录,满足时间和签到要求
	(SELECT 
		DISTINCT uid,
		DATE(in_time) AS dt,
	 -- 这里使用窗口函数可以帮我们找到用户的连续签到开始日期
		ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE(in_time)) AS ranking
	FROM tb_user_log
	WHERE (DATE_FORMAT(in_time, '%Y%m%d') BETWEEN '20210707' AND '20211031') AND artical_id = 0 AND sign_in =1),
	-- 第二张子表：计算用户连续签到的开始日期
t2 AS 
	(SELECT 
		*,
		DATE_SUB(dt, INTERVAL ranking DAY) AS dt_1 
	FROM t1),
	-- 第三章子表：用于记录用户每天登陆所能够获得的coin个数
t3 AS (SELECT 
	*,
	   -- 重新根据用户和连续签到时间进行排序,根据排序得到的数值赋予day_coin值
	CASE ROW_NUMBER() OVER(PARTITION BY uid, dt_1 ORDER BY dt) % 7
		WHEN 3 THEN 3
        WHEN 0 THEN 7
        ELSE 1
	END AS day_coin
FROM t2)
-- 最后根据日期和用户进行分组加总
SELECT 
	uid, 
    DATE_FORMAT(dt, '%Y%m') AS month,
    SUM(day_coin) AS coin
FROM t3
GROUP BY uid, DATE_FORMAT(dt, '%Y%m')
ORDER BY DATE_FORMAT(dt, '%Y%m'), uid;