题解 | 连续签到领金币

#取出每个用户连续登录情况
with a as(select uid,date(in_time) as dt,
row_number() over(partition by uid order by date(in_time)) as rk,
date_sub(date(in_time),interval row_number() over(partition by uid order by date(in_time)) day) as primary_date
from tb_user_log
where DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31'
and artical_id =0
AND sign_in = 1),
#算出每个用户每天的金币
b as (select uid,dt,primary_date,
case row_number() over(partition by primary_date,uid order by dt)%7
when 3 then 3
when 0 then 7
else 1
end as day_coin
from a)
#算出每个用户每月的金币
select uid,date_format(dt,'%Y%m') as month,sum(day_coin) as coin
from b
group by uid,date_format(dt,'%Y%m')
order by date_format(dt,'%Y%m'),uid

参考大佬，逻辑：

1. 计算每个用户每天的金币
1. 先求出每个用户连续登录的基准日期
2. 再对基准日期进行排序，按照正常登录日期和uid（易忘）排序，得到是连续登录的第几天，假设是N
3. 再用N%7（这里实现了如何体现7天为1个周期）,若余数为3，则硬币为3，若余数为7，则硬币为7，其他为1
2. 计算每个用户每月的金币

直接用group by uid，月算出