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

select
    uid,
    date_format(dt,'%Y%m') as month,
    sum(case when rk2%7=3 then 3 
             when rk2%7=0 then 7 
             else 1 end -- 利用签到天数的余数来观察是第几天签到
    ) as coin
-- 根据uid和dt2继续排序，得出连续签到天数
from(
select
    *,
    row_number() over (partition by uid,dt2 order by dt) as rk2
from
-- 根据签到时间排序，同时利用签到时间-排序数生成分组值dt2
(select
    *,
    row_number() over (partition by uid order by dt) as rk,
    dt-row_number() over (partition by uid order by dt) as dt2
from
-- 找到签到用户的签到时间
(select
    distinct uid,
    date(in_time) as dt
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 date_format(dt,'%Y%m'),uid