select uid,date_format(intime,'%Y%m') as month,sum(coin) as coin
from(
select uid,intime ,
(case when cnt%7=3 then 3
when cnt%7=0 then 7
else 1 end) as coin
from(
select uid,intime,count(primary_date) over (partition by uid,primary_date order by intime) as cnt
from(
select distinct uid,date(in_time) as intime,dense_rank() over(partition by uid order by in_time) as rk,
date_sub(date(in_time),interval row_number() over(partition by uid order by in_time) day) as primary_date
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
)b

)c
group by uid,date_format(intime,'%Y%m')
order by month,uid

二刷

  1. a层：求每个用户的基准日期primary_date，注意3个限制条件，where date(in_time) between '2021-07-07' and '2021-10-31' and sign_in =1 and artical_id =0，同时也要把in_time字段取出来，注意要用dense_rank搭配distinct，去除统一用户一天多次登录的情况
  2. b层：求每个用户的每个基准日期的连续次数，这里用count（）over实现，重点是partition by uid,primary_date order by intime，得到这是第几天连续签到
  3. c层：求每个用户的每天的金币情况，(case when cnt%7=3 then 3 when cnt%7=0 then 7 else 1 end) as coin
  4. 最外层：求每个用户每个月的累计金币