hivesql之连续签到领金币数

题目需求

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

结果如下：

user_id (用户id)	sum_coin_cn (金币总数)
101	10
102	4
106	3
104	2
103	2
1010	1
108	1
105	1

需要用到的表：

• 用户登录明细表：user_login_detail

user_id (用户id)	ip_address (ip地址)	login_ts (登录时间)	logout_ts (登出时间)
101	170.139.110.11	2024-08-11 09:00:00	2024-08-17 09:31:00
102	120.225.111.22	2024-08-12 10:00:00	2024-08-17 10:35:00
103	10.139.184.97	2024-08-13 11:00:00	2024-08-17 11:34:00

select t3.user_id,
       sum(t3.coin_cn) sum_coin_cn
from (
         select t2.user_id,
                t2.login_date_rk,
                max(t2.counti_cn) + sum(if(t2.counti_cn % 3 = 0, 2, 0)) + sum(if(t2.counti_cn % 7 = 0, 6, 0)) coin_cn
         from (
                  select t1.user_id,
                         t1.login_date,
                         date_sub(t1.login_date, t1.rk) as                                                           login_date_rk,
                         count(*)
                               over (partition by t1.user_id, date_sub(t1.login_date, t1.rk) order by t1.login_date) counti_cn
                  from (
                           ## 既用group by 又用 row_number  会先去重  然后对去重的结果进行排序
                           select user_id,
                                  date_format(login_ts, 'yyyy-MM-dd')                                              login_date,
                                  row_number()
                                          over (partition by user_id order by date_format(login_ts, 'yyyy-MM-dd')) rk
                           from user_login_detail
                           group by user_id, date_format(login_ts, 'yyyy-MM-dd')
                       ) t1
              ) t2
         group by t2.user_id,
                  t2.login_date_rk
     ) t3
group by t3.user_id
order by sum_coin_cn desc