思路（执果索因）

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

  1. 累积金币数→每日金币数（sum()）
  2. 每日金币数→连续签到天数（case when end) 金币规则：每七天为一周期
  • 第三天——连续天数%7=3 coin=3
  • 第七天——连续天数%7=0 coin=7
  • 其他——coin=1
  1. 连续签到天数→签到日期
  • 排序： 先把每个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

alt

  1. 取变量④参考日期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

alt

  1. 对变量⑥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

alt