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

select uid,date_format(t,'%Y%m') as month,
sum(case rn%7 when 3 then 3 when 0 then 7 else 1 end) as coin
from(select uid,t,dt,row_number() over(partition by uid,dt order by t) rn 
     from(select *,date_add(t,interval - row_number() over(partition by uid order by t) day) as dt 
          from(select distinct uid,date(in_time) as t,sign_in from tb_user_log
               where artical_id=0 and sign_in=1 and date(in_time) between '2021-07-07' and '2021-10-31'
               )a 
          )b 
     )c group by 1,2 order by 2,1

记忆点

1. date_format(time,'%Y%m') 调整time格式，显示结果为年月，如202105
2. rn%7 除余计算，显示结果为余数，如8%7结果为1
3. row_number() over(partition by uid,dt order by t) rn 窗口函数，无需group by，表示按照uid和dt分组后，按照t升序排序的序号（1,2,3...）无重复
4. date_add(time,interval 11 day) 在time基础上增加11天

思路（样例见表格）

1. 用日期减去窗口排序，来判断，是否连续签到（即表格中差值列，相同即为连续签到）
2. 用除余来判断第三天/第七天，分配不同金币数量（即表格中最后列，在连续基础上的签到天数）