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

明确题意：

计算每个用户2021年7月以来每月获得的金币数

问题分解：

筛选符合条件的签到记录：where artical_id = 0 and sign_in = 1 and (date(in_time) between '2021-07-07' and '2021-10-31'

处理连续签到：开窗给dt加一列排序编号，如果连续，签到日期与排序编号的差值是相等的，再加一列存差值，按差值分组，差值相同的一组记录在一个连续签到周期内

计算用户每日获得金币数：开窗给tmp_dt加排序，排序编号即为连续签到天数，case row_number() over(partition by uid,date_sub(dt, interval rn day) order by date_sub(dt, interval rn day)) % 7 when 3 then 3 when 0 then 7 else 1 end

按用户和月份分组：group by 1, 2

计算用户每月获得金币数：sum(coin_day)

细节问题：

结果按月份、ID升序排序：order by 2, 1

select
    uid,
    date_format(dt,'%Y%m') 'month',
    sum(coin_day) coin
from(
select
    t1.*,
    date_sub(dt, interval rn day) tmp_dt,
    case row_number() over(partition by uid,date_sub(dt, interval rn day) order by date_sub(dt, interval rn day)) % 7
    when 3 then 3
    when 0 then 7
    else 1
    end coin_day
from(
select
    distinct uid,
    date(in_time) dt,
    row_number() over(partition by uid order by date(in_time)) rn
from tb_user_log
where artical_id = 0 and sign_in = 1 and (date(in_time) between '2021-07-07' and '2021-10-31')
) t1
) t2
group by 1, 2
order by 2, 1;