思路

  1. 先计算连续登录的天数
  2. 题目解释给出连续7天可获得15个金币,连续3天可以获得5个金币，其余是1天一个金币，分三段计算：
  • 判断大于7天，用连续签到天数除以7，取商（7的倍数）乘15 + 连续签到天数取模7（余数）除以3，取商（3的倍数）乘5 + 连续签到天数取模7再取模3（余下不足3天的天数）
 (15 * truncate(dt_cnt/7 ,0))+(5 * truncate((dt_cnt%7)/3 ,0))+(1 * ((dt_cnt%7)%3))
  • 判断大于3天，连续签到天数除以3，取商（3的倍数）乘5 + 连续签到天数取模7再取模3（余下不足3天的天数）
5 * truncate(dt_cnt/3 ,0)+ 1 * (dt_cnt%3)
  • 余下的部分不到3天，每天签到得1金币
1 * dt_cnt

完整代码：

with sign as (
select uid ,mm,dt,subdate(dt,ra) as dts
from
    (
        select 
        uid
        ,date(in_time) as dt 
        ,date_format(in_time,'%Y%m') as mm
        ,row_number() over(partition by uid order by date(in_time) asc ) ra
    from tb_user_log
    where artical_id=0 
        and sign_in=1
        and date(in_time)>='2021-07-07' and date(in_time)<='2021-10-31'
     )a
)

select
    uid
    ,mm
    ,sum(case when dt_cnt >= 7 then (15 * truncate(dt_cnt/7 ,0))
                                    +(5 * truncate((dt_cnt%7)/3 ,0))
                                    +(1 * ((dt_cnt%7)%3))
          when dt_cnt >= 3 then 5 * truncate(dt_cnt/3 ,0)
                              + 1 * (dt_cnt%3) 
          else 1 * dt_cnt
     end ) as coin  
from ( select uid
            ,mm
            ,dts
            ,count(dt) dt_cnt 
        from  sign group by 1,2,3 ) a
group by 1,2
order by mm,uid