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

WITH t as(
SELECT *
FROM tb_user_log
WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
AND artical_id = 0 AND sign_in = 1
),
t1 as(
SELECT DISTINCT uid,date(in_time) AS dt,
ROW_NUMBER()OVER(PARTITION BY uid ORDER BY date(in_time)) AS ranking
FROM t
),
t2 as(
SELECT uid,dt,ranking,
(dt - ranking) AS continue_dt
FROM t1
),
t3 as(
SELECT uid,dt,ranking,continue_dt,
ROW_NUMBER()OVER(PARTITION BY uid,continue_dt ORDER BY dt) AS continue_ranking
FROM t2
),
t4 as(
SELECT *,
(CASE WHEN continue_ranking % 7 = 3 THEN 3
WHEN continue_ranking % 7 = 0 THEN 7
ELSE 1
END) AS coin
FROM t3
)
SELECT uid,DATE_FORMAT(dt,'%Y%m') AS month,
SUM(coin) AS coin
FROM t4
GROUP BY uid,month
ORDER BY month,uid;