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

# 连续签到 构造   核心：排序编号 与 签到日期的差值是相等的
with table1 as(
    select uid,date_format(in_time,'%Y%m%d') as in_date,
    dense_rank()over(partition by uid order by in_time) as rank_number
    from tb_user_log
    where in_time >='2021-07-07' and in_time <"2021-11_01" and artical_id = 0 and   
    sign_in = 1
),
table2 as(# 核心：排序编号 与 签到日期的差值是相等的
select *,
date_sub(in_date,interval rank_number day) as temp_df,
# 连续签到: 通过 uid 和 temp_df(排序编号和签到日期之差) 分组 排序获得连续签到天数
dense_rank()over(partition by uid,date_sub(in_date,interval rank_number day) order by in_date) as con_day
from table1
),
table3 as (
    select *,
    case when con_day % 7 = 3 then 3
        when con_day % 7 = 0 then 7
        else 1 end as coin_everyday
    from table2
)
select uid,date_format(in_date,'%Y%m') as month,sum(coin_everyday)
from table3
group by uid,month
order by month,uid
# select *
# from table3