--mySQL

WITH log AS
(
    SELECT DISTINCT uid, DATE(in_time) AS dt
    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'
),
log_sign_in AS
(
    SELECT uid, dt, ROW_NUMBER() OVER (PARTITION BY uid ORDER BY dt ASC) AS rm
    FROM log
),
log_sign_coin AS
(
    SELECT
        uid,
        dt,
        DATE_SUB(dt, INTERVAL rm DAY) AS dt_tmp,
        ROW_NUMBER() OVER (PARTITION BY uid, DATE_SUB(dt, INTERVAL rm DAY) ORDER BY dt)  % 7 AS con_sign
    FROM log_sign_in
),
log_coin AS
(
    select
    uid
    ,dt
    ,case when con_sign = 3 then 3
          when con_sign = 0 then 7
     else 1 end coin
    FROM log_sign_coin
)
select
uid
,date_format(dt, '%Y%m') month
,sum(coin) coin
FROM log_coin
group by 1,2
order by 1,2;

--SQL SERVER

Tips：先用CAST函数将日期字段转换为日期类型，然后再使用CONVERT函数将其转换为yyyymmdd的格式

with t1 as
(
    select DISTINCT uid,CONVERT(nvarchar(10),in_time,120) as dt
    from tb_user_log
    WHERE artical_id = 0 and sign_in = 1 and CONVERT(nvarchar(10),in_time,120) >= '2021-07-07' and CONVERT(nvarchar(10),in_time,120) <= '2021-10-31'
),
t2 as
(
    select uid,dt,ROW_NUMBER() over (partition by uid order by dt asc) as rm
    from t1
),
t3 as
(
    select uid,dt
    ,DATEADD(day,-rm,dt) as dt_tmp
    ,ROW_NUMBER() over (partition by uid,DATEADD(day,-rm,dt) order by dt asc) as coin_sign 
    from t2
),
t4 as
(
    select uid,dt
    ,case when coin_sign = 3 then 3
          when coin_sign = 0 then 7
     else 1 end coin
    from t3
)
select uid,CONVERT(varchar(6), CAST(dt AS date),112) as month
,SUM(coin) as sumCoin
from t4
group by uid,CONVERT(varchar(6), CAST(dt AS date),112)
ORDER BY uid,CONVERT(varchar(6), CAST(dt AS date),112)