题解 | 连续签到领金币

#每天金币数
select uid,date_format(d,'%Y%m') as month,sum(day_coin) as coin
from
(select uid,d,
(case when count(primary_date) over(partition by uid,primary_date  order by d) %7 =3 then 3
when count(primary_date) over(partition by uid,primary_date  order by d) %7 =0 then 7
else 1 end) as day_coin
from(select uid,date_format(in_time,'%Y%m%d') as d,date_sub(date_format(in_time,'%Y%m%d'),interval row_number() over(partition by uid order by in_time) day) as primary_date
from tb_user_log
where date_format(in_time,'%Y%m%d') between '20210707' and '20211031'
and artical_id =0
and sign_in =1)a)b
group by uid,date_format(d,'%Y%m')

三刷 count(primary_date) over(partition by uid,primary_date order by d) 标红容易遗忘