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

#忘了思考连续的问题了
#date:[0,1,2,5,7]
#row_number;[1,2,3,4,5]
#row_number-dat:[1,1,1,-1,2]
#按照row_number 进行分组，一个组是一个连续
SELECT uid
       ,date_format(dt,'%Y%m') AS month
       ,sum(coin_cnt)as coin 
FROM(
        SELECT
        uid
        ,dt
        ,case when mod(mod(rnk,7),3)=0  and mod(mod(rnk,7),2)<>0 then 3
              when mod(sgn_cnt,7)=0   then 7
            else 1 end as coin_cnt
FROM(
    SELECT *
         ,dense_rank() over(partition by uid, date_add(dt, interval -sgn_cnt day) order by dt) rnk
        FROM(
            SELECT uid
                ,date_format(in_time,'%Y%m%d')AS dt
                ,dense_rank()OVER (PARTITION BY uid order by  date_format(in_time,'%Y%m%d')asc)AS sgn_cnt
            FROM tb_user_log
            WHERE date_format(in_time,'%Y%m%d')>=20210707

            AND date_format(in_time,'%Y%m%d')<20211101
            AND artical_id=0
            AND sign_in=1
            GROUP BY 1,2
        )AS a
    )AS b
)AS c
GROUP BY uid,month
ORDER BY month,uid