Hive sql 常见面试题-连续签到领金币数

1. 题目需求

用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。
每连续签到7天重新累积签到天数。
从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序

结果如下：

2. 需要用到的表

用户登录明细表：user_login_detail

3. 查询sql

SELECT

  user_id,

  sum(coin) as sum_coin_cn

FROM

  (

    SELECT

      user_id,

      (

        case

          when rn_new % 7 = 3 then 3

          when rn_new % 7 = 7 then 7

          else 1

        end

      ) coin

    FROM

      (

        SELECT

          *,

          date_sub (login_date, rn) date_temp,

          rank() over (

            partition by

              user_id,

              date_sub (login_date, rn)

            order by

              login_date

          ) rn_new

        FROM

          (

            SELECT

              user_id,

              to_date (login_ts) as login_date,

              rank() over (

                partition by

                  user_id

                order by

                  to_date (login_ts)

              ) rn

            FROM

              user_login_detail

            GROUP BY

              user_id,

              to_date (login_ts)

          ) t

      ) t2

  ) t3

GROUP BY

  user_id