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

#需求：计算每个用户2021年7月以来每月获得的金币数
#输出：uid、month、coin(金币数)
#要求：活动时间范围为'2021-07-07'-'2021-10-31';artical_id为0时sign_in值才有效;按月份、ID升序输出;如果in_time和out_time跨天，只记in_time对应的日期签到
#用户每天签到领1金币，并累积签到天数，连续签到的第3、7天分别可额外领2、6金币，每连续7天后重新累积签到天数
select uid,date_format(dt,'%Y%m') 'month',sum(w2) coin
from(
  select*,date_sub(dt,interval ck1 day) w1,
  #tt1得到的是dt日期的连续打卡是从哪一天开始的，得到的是这个开始日期；
  #比如dt的时间为2021-07-07，ck1则是为日期是连续的第几天进行打标；例如07-07是用户连续登录的第二天，那么ck1为2，则date_sub(dt,interval ck1 day)得到的日期为2021-07-05
  case dense_rank()over(partition by date_sub(dt,interval ck1 day),uid order by dt)%7
  when 3 then 3
  when 0 then 7
  else 1
  end w2
  #对于case when中测试表达式的解释：dense_rank以打卡的开始日期和uid分组，以dt排序，得到对当天是连续签到的第几天的排名打标
  #case when对dense_rank%7看余数，when余数为3，说明连续打卡3天，得到3枚金币；余数为0，说明刚好被7整除，连续签到天数是7的倍数，当天得到7枚金币；其它时间每次打卡都是得1枚金币
  #%的意思是取余；例如dense_rank为3，除以7，商数为0，得到余数为3
  #因此，这里的tt2得到的是dt日期当天得到的金币数
  from(
    select distinct uid,date(in_time) dt,
    dense_rank()over(partition by uid order by date(in_time)) ck1
    #窗口函数对用户是上线的第几天进行排序打标
    from tb_user_log
    where artical_id=0 and sign_in=1
    and date(in_time) between '2021-07-07' and '2021-10-31'
  ) e1
) e2
group by 1,2
order by 1,2