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

with t1 as (
    select uid,
    date(in_time) as log_day,
    row_number()over(partition by uid order by in_time) as rk
    from tb_user_log
    where artical_id = 0 and sign_in = 1
    and date_format(in_time,'%Y%m%d') between 20210707 and 20211031
    )

select uid,month, sum(daily_coin) as coin
from
(
    select 
uid,month,cdd,
case
when cdd%7 = 3 then 3
when cdd%7 = 0 then 7
else 1
end as daily_coin
from
    (
    select uid, 
date_format(log_day,'%Y%m') as month, 
date_sub(log_day,interval rk day) as initial_day,
row_number()over(partition by date_sub(log_day,interval rk day),uid) as cdd
from t1
     ) t2
) t3
group by 1,2