SQL连续登录领金币

表：user_login_detail
字段：user_id login_ts

连续签到领金币
每天签到领1金币，连续签到3、7天，分别额外多领2和6个金币
连续签到7天重新累计
需求：求每个用户领取的金币数

select user_id,sum(ct) + sum(coin) total_ct  from (
    select 
      user_id
      ,ct
      ,int(ct / 7) *8 + if((ct % 7) >=3,2,0) coin  -- 额外领取的金币
      
      from (
    
        select user_id,diff_date,count(*) ct
         from (
        
            select user_id,login_date,datediff(login_date,rn) diff_date
             
             from (
                select 
                 user_id,login_date,row_number() over(partition by user_id order by login_date) rn 
                 from (
                    select distinct user_id,date_format(login_ts,'yyyy-mm-dd') login_date from user_login_detail
                ) a 
            ) b 
        ) c group by  user_id,diff_date
    ) d 
) e group by user_id