题解 | #连续签到领金币#自定义函数

with tmp as(
select uid,date(in_time) dt
from tb_user_log
where artical_id = 0 and sign_in = 1
and date(in_time) between '2021-07-07' and '2021-10-31'
order by dt)

select uid,date_format(dt,'%Y%m') as month,
  sum(case when nums in (3,10,17,24,31) then 3
           when nums in (7,14,21,28) then 7
           else 1
           end)
from(
select 
  uid,
  dt,
  if(@pre=date_sub(dt,interval 1 day),@count := @count+1,@count := 1) as nums,
  @pre:=dt
from tmp as l,
    (select  @pre:= null,@count:=1) as pc)t
group by uid,month
order by uid