题解 | 连续签到领金币

# 大牛太神了！跟着大牛的思路写的
# 只有artical_id为0时, sign_in值才有效。
# 用户行为日志表，时间范围是2021-07-07 00:00:00 到 2021-10-31 23:59:59

# 第一步，提取有效登陆日期，去重
with t1 as(
      select distinct uid, 
             date(in_time) as dt,
             row_number() 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
), 
# 如果日期是连续的，那么dt_tmp的值就相等，如果不连续，那么就不同，可以用dt_tmp来分组，分成连续的不同时段.再对每一个uid-dt_tmp的小组里面，分组。
# 第二步，使用连续的天数除以7取余数，如果余0就是7个金币，如果余3就是3个金币， 其他就是1个金币

t2 as(
    select *, 
       DATE_SUB(dt,interval rn day) as dt_tmp,
       case (row_number() over(partition by DATE_SUB(dt,interval rn day), uid order by dt))%7 
        when 0 then 7
        when 3 then 3
        else 1 end as coins            
from t1
)

# 计算每个用户，每月获得的金币数，按月份、ID升序排序。
# 第三步，用t2表，用户-月份分组，求总和

select uid,
       date_format(dt,"%Y%m") as month,
       sum(coins) as coin
from t2
group by uid, date_format(dt,"%Y%m")
order by date_format(dt,"%Y%m"),uid