题解 | #国庆期间每类视频点赞量和转发量#
连续签到领金币
http://www.nowcoder.com/practice/aef5adcef574468c82659e8911bb297f
SELECT
uid,
DATE_FORMAT(dt,'%Y%m') month,
sum(day_coin) coin
from
(SELECT
*,
DATE_SUB(dt,INTERVAL rn day) dt_tmp,
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
(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
) t1) t2
GROUP BY
uid,
DATE_FORMAT(dt,'%Y%m')
ORDER BY
DATE_FORMAT(dt,'%Y%m'),uid
