题解 | 连续签到领金币

WITH valid_log AS (
    select distinct 
        uid, 
        date(in_time) as 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'
),
group_log as (
select uid,dt,
date_sub(dt,interval  row_number() over(partition by  uid order by dt  ) day) as gid
from valid_log
),
rank_in_group as (
select uid,dt, row_number() over(partition by uid,gid order by dt ) as rn
from group_log
)
select uid,date_format(dt,'%Y%m') as month ,
sum(1+
case when rn%7=3 then 2
when rn%7=0 then 6
else 0
end
) as coin
from rank_in_group
group by uid ,month
order by month,uid;