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

select
uid, date_format(dt, '%Y%m') month, sum(coin) coin
from(
    select
    uid, dt, tep,
    case when row_rank%7 = 3 then 3 when row_rank%7 = 0 then 7 else 1 end as coin
    from( 
        select
        uid, dt, tep, row_number() over(partition by uid, tep order by dt) row_rank
        from(
            select
            uid, dt, date_sub(dt,interval rn day) tep
            from(
                select 
                uid, date(in_time) dt, row_number() over(partition by uid order by date(in_time)) rn
                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 by uid, date(in_time)
            ) t
        )k
    ) l
) n
group by date_format(dt, '%Y%m'), uid
order by month, uid