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

with t1 as (select DISTINCT uid,date(in_time) dt,
row_number() over(partition by uid  order by date(in_time) ) rn ,
date(in_time)-(row_number() over(partition by uid  order by date(in_time) )) rn2
from tb_user_log
where artical_id=0 and sign_in=1
and date(in_time) between '2021-07-07' and '2021-10-31')

select uid,date_format(dt,'%Y%m')
,sum(case when rn3%7=3 then 3 when rn3%7=0 then 7 else 1 end ) coin
from
(select *,
row_number() over(partition by uid,rn2 order by dt) rn3
from t1)t2
group by 
uid,date_format(dt,'%Y%m')
order by uid,date_format(dt,'%Y%m')