题解 | 连续签到领金币

-- with内容是 保证同一个用户在同一天多次登录只留下签到那一次的记录
with log_info as(
    select 
        uid,
        date(in_time) log_date,
        sign_in,
        artical_id
    from 
    (
        select
            *,
            row_number() over(partition by uid,date(in_time) order by sign_in desc) rn
        from
            tb_user_log
        where date(in_time) >= '2021-07-07' and date(in_time)<'2021-11-1' and artical_id = 0
    ) t
    where rn = 1 
)
select 
    uid,
    extract(year_month from log_date) month,
    sum(coin_per_day) coin
from 
    (
        select 
        *,
        case
            when rn%7=3 then 3
            when rn%7=0 then 7 
            else 1
        end coin_per_day
    from 
        (
            select 
                uid,
                prim_day,
                log_date,
                row_number() over(partition by uid,prim_day order by log_date) rn
            from
            (
                select
                    uid,
                    date_sub(log_date,interval (rank() over(partition by uid order by log_date)) day)   prim_day,
                    log_date
                from log_info
                where sign_in = 1 
            ) t1
        ) t2
    ) t3
group by uid,month
order by month,uid