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

with tiaojian as (
select 
uid,
date(in_time) as pday
from tb_user_log
where
artical_id=0
and 
sign_in=1
and 
date(in_time) between "2021-07-07" and "2021-10-31"
group by date(in_time),uid
),tiaojian1 as (
select 
uid,
pday,
dense_rank()over(partition by uid,subdate(pday,pn) order by pday) as pdiff
from(
select 
uid,
pday,
dense_rank()over(partition by uid order by pday) as pn
from tiaojian
) as t 
)

select 
uid,
date_format(pday,"%Y%m"),
sum(case when pdiff%7=3 then 3 
         when pdiff%7=0 then 7 else 1 end) as coin
from tiaojian1
group by uid,date_format(pday,"%Y%m")
order by date_format(pday,"%Y%m") asc,uid