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

with t as (
    select uid,date(in_time) as dint,
           date(in_time)-dense_rank()over(partition by uid order by in_time) as start_time
    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,date_format(dint,"%Y%m") as month,sum(case rn%7 when 3 then 3 when 0 then 7 else 1 end) as coin
FROM
    (
      select *,
      dense_rank()over(partition by uid,start_time order by dint) as rn
      from t
    ) t1
group by uid,month
order by month,uid