38连续签到领金币数问题求解

题目：

题解：

select
  user_id,
  sum(coin) as sum_coin_cn
from (
    select
      *,
      --特殊日期给的多，其他日期都是1个，注意取模运算法则3%7=3
      case
          when rn2 % 7 = 3 then 3
          when rn2 % 7 = 0 then 7
          else 1 end as coin
    from (
        select
          *,
          --标识的是1个连续登录时间段内的顺序
          row_number() over (PARTITION by user_id,next_date order by rn) as rn2
        from (
            select
              *,
              date_sub(login_date,rn) as next_date--连续登录next_date值相同
            from (
                select
                  *,
                  row_number() over(PARTITION by user_id order by login_date) as rn
                from (
                    select DISTINCT user_id, date(login_ts) as login_date
                    from user_login_detail
                  ) t1
              ) t2
          ) t3
      ) t4
  ) t5
group by
  user_id