with t1 as(select a.uid, a.dt, a.rank_, dense_rank() over(partition by uid,a.dt-a.rank_ order by date(a.dt)) rank_1
from 
(select uid, date(in_time) dt, dense_rank() over(partition by uid order by date(in_time)) rank_
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and sign_in=1
and artical_id=0)a
),
t2 as(select uid, dt, rank_1, 
case when rank_1%7=3 then 3 when rank_1%7=0 then 7 else 1 end coin
from t1)
select uid, date_format(dt,'%Y%m') month, sum(coin)
from t2
group by uid,month
order by month,uid
  • 连续日期用dense_rank和日期相减得到一个同一个日期，然后在用一次dense_rank得到连续日期的天数
  • 使用case when 得到对应的金币数
  • 第3天得到3金币
  • 第7天得到7金币
  • 注意限制
  • artical_id=0
  • sign_in=1