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

select uid,month,sum(gold) as coin
from 
(select uid,date_format(dt,'%Y%m') as month,
case mod(rank() over(partition by uid ,start order by dt ),7) when 3 then 3  when 0 then 7 else 1 end  as gold
from 
(select uid,date_sub(dt,interval rk-1 day) start ,dt
from 
(select distinct uid,date(in_time) as dt,dense_rank() over(partition by uid order by date(in_time)) as rk
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'  and sign_in =1
and artical_id = 0)  base) tmp) t
group by uid,month
order by month,uid

2021年7月7日0点开始