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

select uid,in_month,
        sum(case
        when days%7 in (1,2)    then 15*floor(days/7)+days%7
        when days%7 in(3,4,5,6) then 15*floor(days/7)+days%7+2
        else 15*floor(days/7) end) as coin

from(
    select uid,idate, 
            min(date_format(in_day,'%Y%m')) as in_month, # 为了最后得到月份列
            count(distinct in_day) as days # 连续登录日期
    from(
        select uid, in_day,date_sub(in_day,interval irank day) as idate
        from(
            select uid, in_day,
                    row_number() over(partition by uid order by in_day)
                    as irank
            from(
                select uid, date(in_time) as in_day
                from tb_user_log
                where date(in_time) between '2021-07-07' and '2021-10-31'
                        and artical_id = 0 and sign_in = 1
                ) as t1
            )as t2
        )as t3
    group by uid,idate
    ) as t3
group by uid,in_month
order by in_month,uid