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

• 难点：连续n次的筛选判定
  • 解决思路：可以参考计算连续出现3次的所有数字。
  • 构建自增序列，与原序列相减得到序列差。
  • 序列差相同的意味着序列连续，序列差不同则意味着序列不是连续

select uid,
       month,
       sum(coin) as coin
from (
         select uid,
                month,
                min_date_diff - date_id                                                          as id_diff,
                count(*) div 7 * 15 + if(count(*) mod 7 > 2, count(*) mod 7 + 2, count(*) mod 7) as coin
         from (
                  select uid,
                         date_format(in_time, '%Y%m')                                                        as month,
                         row_number() over (partition by uid, month(in_time) order by in_time) as date_id,
                         datediff(in_time, min(in_time) over (partition by uid, month(in_time) order by in_time)) +
                         1                                                                     as min_date_diff
                  from tb_user_log
                  where artical_id = 0
                    and sign_in = 1
                    and in_time between '2021-07-07' and '2021-11-01'
              ) as t
         group by uid, month, min_date_diff - date_id
     ) as u
group by uid, month
order by month, uid;