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

# 对同一个uid的连续日期进行切片，然后对每一个切片内进行排序并对7取余，按照余数分配金币
# 3. 统计连续签到领取金币
select uid, dt as month, sum(coinper) as coin from(
    # 2. 计算每个用户每天的金币
    # 2.2 对同一个uid的连续日期进行切片:对同一个用户，相同group_id的数据按dt排序并对7取余
    select uid, dt, case row_number() over(partition by uid,group_id order by dt)%7
    when 3 then 3
    when 0 then 7
    else 1
    end as coinper from (
        # 2.1 对同一个uid的连续日期进行切片: 使用窗口函数构造切片标签group_id
        select uid, date_format(dt,"%Y%m") as dt, 
        date_add(dt, interval -row_number() over(partition by uid order by dt) DAY) as group_id 
        from(
            # 1. 查询有效记录
            select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log
            where artical_id = 0 and sign_in = 1 
			and (date_format(in_time,"%Y-%m-%d") between '2021-07-07' and '2021-10-31')
        ) t
    )t1
)t2
group by uid, month
order by month, uid