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

思路：
①如何判断连签：用签到日期-日期排序=连签日期（连签日期一致表明连续签到），对连签日期计数即得到连签天数。
②连签天数以7天一个周期，每个周期里第3天、第7天得3分、7分，其余得1分，用%7进行取余判断。

with tb1 as(
select uid, date(in_time) dt, 
date_sub(date(in_time),interval rank()over(partition by uid order by date(in_time)) day) r
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31' and artical_id=0 and sign_in=1)

select uid, `month`, sum(num)
from(
select uid, date_format(dt,'%Y%m') `month`, 
case when l_dt%7=3 then 3
when l_dt%7=0 then 7
else 1 end as num
from(select uid, dt, count(r)over(partition by uid, r order by dt) l_dt from tb1) tb2
) tb3
group by uid, `month`
order by `month`, uid