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

select uid,date_format(date1,'%Y%m') month,
sum(CASE
    WHEN sign_in = 1 AND datediff(date1,date3)%7=3 THEN 3
    WHEN sign_in = 1 AND datediff(date1,date3)%7=0 THEN 7
    WHEN sign_in = 0 THEN 0
    ELSE 1
    END) AS coin
from(
SELECT uid,date1,date_sub(min(date1) over(partition by uid,date2),interval 1 day) date3,
       sign_in
FROM (
         SELECT uid, date1, DATE_SUB(date1, INTERVAL row_number() OVER (PARTITION BY uid ORDER BY date1) DAY) AS date2, sign_in
         FROM (
                  SELECT DISTINCT uid, DATE(in_time) AS date1, sign_in
                  FROM tb_user_log
                  WHERE artical_id = 0 AND in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59'
              ) t
     ) tt
) ttt
group by uid,month

太久不写sql，一些函数的用法都忘了

datediff(end_date,start_date)，别把参数位置记反了

细节问题：

3%7=3,-3%7=-3,7%7=0,-7%7=0

date_sub(min(date1) over(partition by uid,date2),interval 1 day) 计算连续签到的第一天的上一天，这样做datediff得到的值就是第几天