Hive sql 每天场景题37-38

37、用户等级：
忠实用户：近7天活跃且非新用户
新晋用户：近7天新增
沉睡用户：近7天未活跃但是在7天前活跃
流失用户：近30天未活跃但是在30天前活跃
假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数

结果如下：

level <string> （用户等级）	cn <bigint> （用户数量)
忠实用户	6
新增用户	3
沉睡用户	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

代码：

with t as (select 
user_id
,min(substr(login_ts,1,10)) as frist_login_ts
,max(substr(login_ts,1,10)) as recent_login_ts
from user_login_detail
group by user_id)

select 
level
,count(user_id) as cn
from (
select 
user_id
,case when (datediff('2021-10-09',recent_login_ts)<=7 and datediff('2021-10-09',frist_login_ts) >7) then '忠实用户'
 when datediff('2021-10-09',frist_login_ts) <=7 then '新增用户'
 when datediff('2021-10-09',recent_login_ts) between 6 and 30 then '沉睡用户' 
 else '流失用户' end as level 
 from t
)a
group by level

38、用户每天签到可以领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

代码

with t as (
select 
*,count(*) over(partition by user_id, olddate) as lianxudays
 ,count(distinct login_ts) over(partition by user_id )as days
from (
select * 
,date_add(login_ts,-rn) as olddate
from (
select  user_id,substr(login_ts,1,10) as login_ts 
,row_number() over(partition by user_id order by substr(login_ts,1,10) ) as rn
from user_login_detail
) b
)a)
, t2 as (
select 
-- 连续签到分二种情况：1、连续超过七天的， 2、未连续超过七天的 
user_id
,days
,cast (lianxudays /7 as int )+1 as week_cnt
,case when (lianxudays % 7) < 3 then 0 
      when (lianxudays % 7) between 3 and 6 then 2 
      else  8 end  external_cnt
from t 
group by 
user_id
,days
,cast (lianxudays /7 as int )+1 
,case when (lianxudays % 7) < 3 then 0 
      when (lianxudays % 7) between 3 and 6 then 2 
      else  8 end
)

select 
user_id
,days + external_cnt*week_cnt as sum_coin_cn
from 
(
select 
user_id
,days
,week_cnt
,sum(external_cnt)as external_cnt
  from t2
  group by 
  user_id
,days
,week_cnt
)a