题解 | 连续签到领金币

select
    uid, month, sum(coin) as coin
from (
    select
        uid,
        month,
        (
            case
                when (cons_days <= 7) then (
                    case 
                        when (cons_days between 1 and 2) then 1*cons_days
                        when (cons_days between 3 and 6) then 2 + 1*cons_days
                        else 15
                    end
                )
                when cons_days > 7 then (15*floor(cons_days/7) + (
                    case 
                        when (cons_days%7 between 1 and 2) then 1*(cons_days%7)
                        when (cons_days%7 between 3 and 6) then 2 + 1*(cons_days%7)
                    end
                ))
            end
        ) as coin
    from (
        select
            uid,
            month,
            count(diff) as cons_days
        from (
            select
                uid,
                date_format(dt, '%Y%m') as month,
                date_sub(dt, interval rk day) as diff
            from (
                select 
                    distinct uid,
                    date(in_time) dt,
                    row_number() over(partition by uid order by date(in_time)) as rk
                from tb_user_log
                where artical_id = 0 and sign_in = 1 and (in_time >= '2021-07-07 00:00:00' and in_time < '2021-11-01 00:00:00')
            )as t1
        )as t2
        group by uid, month, diff
    )as t3
)as t4
group by month, uid
order by month, uid