题解 | 连续签到领金币

with
    t1 as (
        select distinct
            uid,
            date (in_time) as dt,
            dense_rank() over (
                partition by
                    uid
                order by
                    date (in_time)
            ) as dt1
        from
            tb_user_log
        where
            sign_in = 1
            and date (in_time) >= 20210707
            and date (in_time) < 20211101
            and artical_id=0
    )
select
    uid,
    date_format (dt, '%Y%m') as month,
    sum(
        CASE
            WHEN MOD(ddt, 7) = 3 THEN 3 -- 使用标准MOD函数替代%
            WHEN MOD(ddt, 7) = 0 THEN 7 -- 避免%符号导致的解析错误
            ELSE 1 -- 必须指定ELSE默认值
        END
    ) AS coin
from
    (
        select
            uid,
            dt,
            row_number() over (
                partition by
                    uid,
                    date_sub(dt, interval dt1 day)
            ) as ddt
        from
            t1
    ) tt
group by
    uid,
    month
    order by month ,uid