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

select
    uid,
    date_format(dt,'%Y%m') as month,
    sum(case when rk2=3 then 3 when rk2=0 then 7 else 1 end) as coin # 用户每天签到可以领1金币，连续签到的第3、7天分别领3、7金币。每连续第8天签到时记为新的一轮签到的第一天，领1金币
from (
    select
    uid,
    dt,
    dense_rank()over(partition by uid,rk1 order by dt)%7 as rk2
from (
     select
        uid,
        date(in_time) as dt,
        date_sub(date(in_time), interval dense_rank()over(partition by uid order by date(in_time)) day ) as rk1 # 跨天了，也只记作in_time对应的日期签到了
    from tb_user_log 
    where date(in_time) between '2021-07-07' and '2021-10-31' #2021年7月7日0点开始（该活动到10月底结束，11月1日开始的签到不再获得金币）
    and artical_id =0 and sign_in=1 # artical_id为0时sign_in值才有效。
) as t
) as t1
group by uid,month
order by month,uid # 结果按月份、ID升序排序。