Hive 刷题——连续签到送金币

需求描述

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

结果如下：

user_id <string> （用户id）	sum_coin_cn <bigint> （金币总数)
101	7
109	3
107	3
102	3
106	2
104	2
103	2
1010	2
108	1
105	1

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

 

参考实现

select user_id, sum(case when rn = 3 then 3 when rn = 7 then 7 else 1 end) sum_coin_cn
from (select user_id, next_date, row_number() over (partition by user_id,next_date order by rn) rn
      from (select user_id, login_date, rn, date_sub(login_date, rn) next_date
            from (select user_id, login_date, row_number() over (partition by user_id order by login_date) rn
                  from (select user_id, date(login_ts) login_date
                        from user_login_detail
                        group by user_id, date(login_ts)) y) y) t) t
group by user_id;