题解 | SQL 167. 连续签到领金币

WITH t1 AS( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重
	SELECT
		DISTINCT uid,
		DATE(in_time) dt,
		DENSE_RANK() over(PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号
	FROM
		tb_user_log
	WHERE
		DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1
),
t2 AS ( # t2 就是求出
	SELECT
	*,
	DATE_SUB(dt,INTERVAL rn day) dt_tmp,
	DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt ) AS new_num,
	case DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7 -- 再次编号
		WHEN 3 THEN 3
		WHEN 0 THEN 7
		ELSE 1
	END as day_coin -- 用户当天签到时应该获得的金币数
	FROM
	t1
),
t3 AS (SELECT uid,
              DATE_FORMAT(dt, '%Y%m') `month`,
              sum(day_coin)           coin -- 总金币数
       FROM t2
       GROUP BY uid, DATE_FORMAT(dt, '%Y%m')
       ORDER BY DATE_FORMAT(dt, '%Y%m'), uid
)
SELECT * FROM t3;

# 这道题的关键在于，减差后的时间，id才是排序分类基准点，排序照常按照dt时间！