题解 | #连续签到领金币#

with t1 as(
SELECT
	uid,
	DATE ( in_time ) AS dt,
	row_number() over (
		PARTITION BY uid 
	ORDER BY
	DATE ( in_time )) AS rn,
	date_sub( DATE ( in_time ), INTERVAL row_number() over ( PARTITION BY uid ORDER BY DATE ( in_time )) DAY ) AS con_t 
FROM
	tb_user_log 
WHERE
	artical_id = 0 
	AND sign_in = 1 
	AND DATE ( in_time )>= '2021-07-07' and date(in_time)<='2021-10-31'
),	
t2 as (
select *, rank ()over (PARTITION by uid,con_t order by dt) as con_t1
 from t1),

t3 as (
select *,DATE_FORMAT(dt,'%Y%m') as month,case when con_t1 %7=0 then 7 when con_t1 %7=3 then 3 else 1 end as coin from t2)


select uid,month,sum(coin) as coin from t3 
group by uid,month