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

select uid
        ,date_format(dt,'%Y%m') AS month
        ,sum(case rk%7 when 3 then 3 when 0 then 7 else 1 end) AS coin
from(
    select uid
        ,dt
        ,date_sub(dt,interval rn day) AS dt_fd
        ,dense_rank() over(partition by date_sub(dt,interval rn day),uid order by dt asc) AS rk
    from(
        select distinct uid
            ,date(in_time) AS dt
            ,dense_rank() over(partition by uid order by date(in_time) asc) AS rn
        from tb_user_log
        where sign_in = 1 and artical_id = 0 and date(in_time) between '2021-07-07' and '2021-10-31'
    )t
)t1
group by uid
        ,date_format(dt,'%Y%m')
order by date_format(dt,'%Y%m')
        ,uid

1. 按照顺序用rn标记每个uid总共签到了多少次（不管是否连续签到）
2. 签到日期dt-rn=dt_fd . 如果dt_fd相同，则说明这些记录是连续签到的，对这些记录partition by dt_fd,uid做开窗函数row_number()rk即可知道连续签到的天数是多少(每个开窗组内最大的rk即为最大的连续签到天数)
3. rk%3=3 then coin=3 rk%7=0 coin=7 else coin = 1
4. 最终group by uid,dt sum(coin)即可求出每个用户的金币数量