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

步骤1：获取在指定时间段内以及artical_id为0的登录时间+签到表

with t1 as (
    select 
        uid
        ,date(in_time) as dt
        ,sign_in
    from tb_user_log
    where date(in_time) >= '2021-07-07' and date(in_time) <= '2021-10-31'
    and artical_id = 0
)

步骤2：用窗口函数技巧获取连续签到日

, t2 as (
    select 
        uid
        ,date_format(dt, '%Y%m') as month
        ,dt
        ,date_sub(dt, interval row_number() over(partition by uid order by dt) day) as continue_date
    from t1
    where sign_in = 1  --指定只记录签到日
)

步骤3：使用窗口函数得到当前签到日是连续签到的第几天，然后按照连续登陆的天数，记录每天的金币数

, t3 as (
    select 
        uid
        ,month
        ,case row_number() over(partition by uid, continue_date order by dt)%7 
              when 3 then 3
              when 0 then 7
              else 1
        end as daily_coin
    from t2
)

步骤4：对连续签到日的每日签到得到的金币数求和

select 
    uid
    ,month
    ,sum(daily_coin) as coin
from t3
group by uid, month  
order by month, uid asc;

全体代码：

# 获取签到+登录表
with t1 as (
    select 
        uid
        ,date(in_time) as dt
        ,sign_in
    from tb_user_log
    where date(in_time) >= '2021-07-07' and date(in_time) <= '2021-10-31'
    and artical_id = 0
)
# 获取连续签到日
, t2 as (
    select 
        uid
        ,date_format(dt, '%Y%m') as month
        ,dt
        ,date_sub(dt, interval row_number() over(partition by uid order by dt) day) as continue_date
    from t1
    where sign_in = 1
)
, t3 as (
    select 
        uid
        ,month
        ,case row_number() over(partition by uid, continue_date order by dt)%7 
              when 3 then 3
              when 0 then 7
              else 1
        end as daily_coin
    from t2
)

select 
    uid
    ,month
    ,sum(daily_coin) as coin
from t3
group by uid, month  
order by month, uid asc;