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

1.提取uid,日期,和不同uid对应的日期排名，注意筛选条件 2.根据上表将同一UID，且日期连续的进行分组，进行计算，得到每一组的具体硬币情况 3.进行汇总，按照uid和月份分组。

select uid,date_format(dt,"%Y%m") as month,sum(coin)
from
(select uid,date_sub(dt,interval rk-1 day) as first_day,
case dense_rank()over(partition by uid,date_sub(dt,interval rk-1 day) order by dt)%7 when 0 then 7
when 3 then 3 else 1 end as coin,
dt 
from
(select uid,date(in_time) dt,dense_rank()over(partition by uid order by date(in_time)) rk from tb_user_log
where sign_in=1 and artical_id=0 and date(in_time) between "2021-07-07" and "2021-10-31")t1)t2
group by uid,date_format(dt,"%Y%m") 
order by month,uid;