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

解决了连续后面就好写了；

  SELECT 
	uid,
	DATE_FORMAT(dt,'%Y%m') AS MONTH,
	SUM(coins) AS coin
  FROM
  (
	 SELECT
		uid,first_day,dt,rn,
		CASE rn WHEN  3 THEN 3
			WHEN  0 THEN 7
		ELSE 1 END AS coins
	  FROM
		  (
		    SELECT
			uid,first_day,dt,
			ROW_NUMBER() OVER(PARTITION BY  uid,first_day ORDER BY dt) % 7 rn
		  FROM
		  (
			  SELECT
				uid,rn,dt,DATE_SUB(dt,INTERVAL rn DAY) AS first_day
			  FROM 
			  (
				  SELECT
					uid,
					ROW_NUMBER() OVER(PARTITION BY uid ORDER BY in_time) rn,
					DATE_FORMAT(in_time,'%Y%m%d') dt
				  FROM tb_user_log tl
				  WHERE artical_id = 0 AND sign_in = 1  
                  AND DATE_FORMAT(in_time,'%Y%m%d') >= '20210707'
                  and DATE_FORMAT(in_time,'%Y%m%d') < '20211101'
			  ) t  
		  ) t1
	  ) t2  
  )  t3
  GROUP BY uid,MONTH