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

SELECT uid, month, SUM(every_partition_coin) coin FROM
(SELECT uid, month, (CASE WHEN MOD(continue_count,7)>=3
                     THEN FLOOR(continue_count/7)*8+2+continue_count
                     WHEN MOD(continue_count,7)<3 
                     THEN FLOOR(continue_count/7)*8+continue_count END) every_partition_coin
 FROM
 (SELECT uid, month, COUNT(*) continue_count FROM
  (SELECT uid, dt, month, ROW_NUMBER() over (partition by uid order by dt) rk FROM 
   (SELECT DISTINCT uid, DATE_FORMAT(in_time,'%Y-%m-%d') dt, DATE_FORMAT(in_time,'%Y%m') month
    FROM tb_user_log
    WHERE artical_id=0 AND sign_in=1
    AND DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31') t1
   ) t2
  GROUP BY uid, month, DATE_SUB(dt, INTERVAL rk DAY)
  ) t3
 ) t4
GROUP BY uid, month 
ORDER BY month, uid

用找#某宝店铺连续2天及以上购物的用户及其对应的天数#的思路 https://blog.nowcoder.net/n/19758260181f4018b24e1c2edf690f32

这里有一个小坑是，如果用in_time BETWEEN '2021-07-07' AND '2021-10-31'是不对的，因为这样是指in_time BETWEEN '2021-07-07 00:00:00' AND '2021-10-31 00:00:00'的意思，会漏掉10-31的时间。（between and 包括两端值）