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

with t as(
    select distinct date_format(in_time, '%Y%m') dt, uid, row_number()over(partition by uid order by date_format(in_time, '%Y%m%d')) rk, date_sub(date_format(in_time, '%Y%m%d'), interval row_number()over(partition by uid order by date_format(in_time, '%Y%m%d')) day) f_dt
    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, mon month, sum(case
when cou_day < 3 then cou_day 
when cou_day >= 3 and cou_day <= 6 then cou_day + 2
when cou_day >= 7 and mod(cou_day, 7) < 3 then 15 * (cou_day div 7) + mod(cou_day, 7)
when cou_day >= 7 and mod(cou_day, 7) >= 3 and mod(cou_day, 7) <= 6 then 15 * (cou_day div 7) + mod(cou_day, 7) + 2
end) as coin
from
(select uid, min(dt) mon, count(f_dt) cou_day
from t
group by uid, f_dt) t_day
group by uid, mon
order by uid, mon