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

思路（执果索因）

累积金币数→每日金币数→连续签到天数→签到日期

1. 累积金币数→每日金币数（sum()）
2. 每日金币数→连续签到天数（case when end) 金币规则：每七天为一周期

• 第三天——连续天数%7=3 coin=3
• 第七天——连续天数%7=0 coin=7
• 其他——coin=1

3. 连续签到天数→签到日期

• 排序： 先把每个uid的签到日期按顺序排列，得到rn1
• 怎么判断连续——分组： 若某几个日期是连续的，那么日期-rn1应是同一天，不妨记这一天为参考日期（ref_date），并以此分组，同一组的就是连续的日期。
• 连续第几天——组内排序： 每组在组内排序rn2即为连续天数 综上，需要依次得到①uid，②签到日期sign_date，③每个uid总体日期排序rn1，④参考日期ref_date，⑤连续天数rn2，⑥当天金币数coin_num，⑦总金币数coin

代码

1. 取变量①uid，②签到日期sign_date，③每个id总体日期排序rn1，得到q1（注意“article_id=0”时sign_in值才有效+2021.7.7-2021.10.31）

select distinct uid,date(in_time) as sign_date,
 row_number() over (partition by uid 
                    order by date(in_time) ) as rn1
 from tb_user_log
 where in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59'
 and sign_in=1 and artical_id=0
 order by uid,sign_date

2. 取变量④参考日期ref_date，⑤连续天数rn2，⑥当天金币数coin_num，得到q2（实际代码中④⑤⑥是依次嵌套的，只取⑥即可，这里为了解释得更清楚，把几个量都展示出来）

with q1 as
(select distinct uid,date(in_time) as sign_date,
 row_number() over (partition by uid 
                    order by date(in_time) ) as rn1
 from tb_user_log
 where in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59'
 and sign_in=1 and artical_id=0
 order by uid,sign_date
)
select uid,sign_date,rn1,
 date_sub(sign_date,interval rn1 day) as ref_date,
 row_number() over (partition by uid,date_sub(sign_date,interval rn1 day) 
                    order by sign_date) as rn2,
case row_number() over (partition by uid,date_sub(sign_date,interval rn1 day) 
                    order by sign_date)%7
    when 3 then 3
    when 0 then 7
    else 1 end as coin_num
    from q1

3. 对变量⑥coin_num求和得到⑦coin

with q1 as
(select distinct uid,date(in_time) as sign_date,
 row_number() over (partition by uid 
                    order by date(in_time) ) as rn1
 from tb_user_log
 where in_time between '2021-07-07 00:00:00' and '2021-10-31 23:59:59'
 and sign_in=1 and artical_id=0
 order by uid,sign_date
),
q2 as 
(select uid,sign_date,rn1,
 date_sub(sign_date,interval rn1 day) as ref_date,
 row_number() over (partition by uid,date_sub(sign_date,interval rn1 day) 
                    order by sign_date) as rn2,
case row_number() over (partition by uid,date_sub(sign_date,interval rn1 day) 
                    order by sign_date)%7
    when 3 then 3
    when 0 then 7
    else 1 end as coin_num
    from q1)        
  
select uid,date_format(sign_date,'%Y%m') as month,sum(coin_num) as coin
from q2
group by uid,month
order by month,uid