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

select 
b.uid,
date_format(b.in_time,'%Y%m'),
sum(b.day_num)
from
(
select
a.uid,
a.in_time,
row_number() over(partition by a.uid,a.dt_tmp),
case when row_number() over(partition by a.uid,a.dt_tmp)%7=0 then 7
 when row_number() over(partition by a.uid,a.dt_tmp)%7=3 then 3
 else 1 end as day_num
from
(
select 
uid,
date(in_time) in_time,
row_number() over(partition by uid order by date(in_time)) rn,
date_sub(date(in_time),interval 
row_number() over(partition by uid order by date(in_time)) day) dt_tmp
from tb_user_log
where artical_id=0 and sign_in=1 
and date(in_time)>='2021-07-07' and date(in_time)<='2021-10-31'
) a
) b
group by b.uid,date_format(b.in_time,'%Y%m')
order by date_format(b.in_time,'%Y%m'),b.uid