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

这一题真的好综合，就是看妮是会灵活运用。感谢网友王尼玛呢提供的思路。
思路：

1. 计算每位用户连续签到的天数

• 统计用户的签到情况，生成签到表
  • 条件：where date(in_time) between '2021-07-07' and '2021-10-31'
    and artical_id=0
    and sign_in=1
  • 对签到时间排序dense_rank() over(partition by uid order by dt) as rn

2. 根据连续签到天数计算当天得到的硬币

• 如果连续签到，则有，签到日期-排序=定值
  • date_sub(dt,interval rn day) -- 定值
  • 继续对得到的定值进行排序，分用户,得到连续天数:dense_rank() over(partition by uid,定值 order by dt) as rn2
  • 根据连续天数rn2,计算当天应该得到的金币数量：case rn2%7 when 3 then 3 when 0 then 7 else 1 end as day_coin

3. 根据2 ，计算得到的总硬币数量：

• sum()
• 分组
• 排序

-- 生成签到表
with t1 as(
select 
    uid,
    date(in_time) as dt,
    dense_rank() over(partition by uid order by date(in_time)) as rn
from tb_user_log
where date(in_time) between '2021-07-07' and '2021-10-31'
and artical_id=0
and sign_in=1
),

-- 金币获得表
t2 as(
select
    uid,
    dt,
    date_sub(dt,interval rn day) as dt2,
    (case dense_rank() over(partition by date_sub(dt,interval rn day),uid order by dt)%7
        when 3 then 3
        when 0 then 7
        else 1 end) as day_coin
from t1
)

-- 计算最后结果
select 
    uid,
    date_format(dt,"%Y%m") as month,
    sum(day_coin) as coin
from t2
group by uid,month
order by month,uid