题解 | 连续签到领金币

select-- 按月计算金币数
    uid
    ,date_format(dt,'%Y%m') 'month'
    ,sum(case 
            when n=3 then 3
            when n=0 then 7
            else 1 end) as gold_num 
from
(-- 重置天数
    select
        uid
        ,dt 
        ,sign_day%7 as n 
    from
    (  -- 判断是连续的第几天
        select
            uid
            ,dt 
            , row_number()over(partition by t111.uid,t111.dt_sub order by t111.dt) as sign_day 
        from
        (   -- 判断连续 
            select
                uid
                ,dt
                ,date_sub(dt, interval dr day) as dt_sub -- 计算日期和排序差值
            from
            ( -- 日期排序
                select 
                    uid
                    , date(in_time) dt
                    , row_number()over(partition by uid order by date(in_time)) as dr
                from tb_user_log
                where in_time between '2021-07-07 00:00:00' and '2021-11-1 00:00:00' 
                and artical_id=0 and sign_in=1
            ) t1111
            group by 
                uid
                ,dt
                ,date_sub(dt, interval dr day) 
        ) t111
    ) t11
) t1 
group by 
    uid
    ,date_format(dt,'%Y%m')
order by
    uid
    ,date_format(dt,'%Y%m')