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

#小白另类解法，好理解，请大牛多多指教
with tiaojian as (
select
uid,
artical_id,
date(in_time) as day,
lead(date(in_time),1)over(partition by uid order by date(in_time)) as pm
from tb_user_log
where
artical_id=0
and 
date(in_time) between "2021-07-07" and "2021-10-31"
and
sign_in=1
)

select 
t1.uid,
date_format(t1.day,"%Y%m"),
sum(case when pct%7=3 then 3 when pct%7=0 then 7 else 1 end)
from(
select 
t.uid,
t.day,
dense_rank()over(partition by t.uid,t.pt order by t.day) as pct
from(
select 
uid,
day,
sum(case when datediff(pm,day)=1 then 0 else 1 end)over(partition by uid order by day desc) as pt
from tiaojian
) as t 
) as t1 
group by date_format(t1.day,"%Y%m"),t1.uid
order by date_format(t1.day,"%Y%m"),t1.uid