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

select t4.uid,t4.month,sum(t4.coin) from (
# 生成登录天数对应金币数
    select t3.uid,date_format(t3.sym,'%Y%m') as month,
(case when t3.rank_sym % 7 = 3 then 3 when t3.rank_sym % 7 = 0 then 7 else 1 end ) as coin 
from (
# 将连续登录sym标识进行排序，用于计算金币数量
    select t2.uid,t2.sym,
row_number() over (partition by t2.uid,t2.sym) as rank_sym from (
# 将排序和日期相减，获取连续登录的部门（sym一样则为连续登录）
    select t1.uid,date_sub(t1.in_time,interval t1.rank_time day) as sym
,t1.rank_time 
from (
# 将日期排序，作为识别连续登录的标识，去重
select  distinct u1.uid,date_format(u1.in_time,'%Y-%m-%d') as in_time,
(DENSE_RANK() over (partition by u1.uid order by u1.in_time)-1) as rank_time  
from tb_user_log as u1
where date_format(u1.in_time,'%Y-%m-%d') between '2021-07-07' and '2021-10-31' 
and u1.artical_id=0 and u1.sign_in=1
) t1) t2) t3 ) t4 group by t4.uid,t4.month