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

select 
uid,
date_format(dt,'%Y%m') as month,
sum(day_coin) as coin
from
    (select 
    uid,dt,
    case (rk1%7)
         when 3 then 3
         when 0 then 7
         else 1
    end as day_coin
    from
        (select t1.*,
            row_number() over(partition by uid,date_sub(dt,interval rk day) order by dt) rk1
        from
            (select distinct uid,date(in_time) as dt,
                    dense_rank() over(partition by uid order by date(in_time)) as rk
            from tb_user_log
            where artical_id=0 and sign_in=1 and
            (DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31')) t1) t2) t3
group by uid,date_format(dt,'%Y%m')
order by uid,month

1. t1表，利用dense_rank对用户进行分组，得出组内日期排名rk
2. t2表，在得到排名之后，利用日期与rk相减，连续日期相减结果会相同，再用row_number() 函数对连续日期进行排名得到rk1
3. t3表，在得到t2排名之后，利用case when 函数，得到每个用户每一天可获得的金币值，新生成一列 day_coin
4. 最终对用户和月份进行分组，得到每名用户每月的金币值