37、用户等级： 忠实用户：近7天活跃且非新用户 新晋用户：近7天新增 沉睡用户：近7天未活跃但是在7天前活跃 流失用户：近30天未活跃但是在30天前活跃 假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数
结果如下：
level
cn
忠实用户
新增用户
沉睡用户
需要用到的表：
用户登录明细表：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) then '忠实用户'
when datediff('2021-10-09',frist_login_ts) 38、用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。每连续签到7天重新累积签到天数。从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序结果如下：user_id（用户id）sum_coin_cn（金币总数)1011091071021061041031010108105需要用到的表：用户登录明细表：user_login_detailuser_id(用户id)ip_address(ip地址)login_ts(登录时间)logout_ts(登出时间)101180.149.130.1612021-09-21 08:00:002021-09-27 08:30:00102120.245.11.22021-09-22 09:00:002021-09-27 09:30:0010327.184.97.32021-09-23 10:00:002021-09-27 10:30:00with 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
Original: https://blog.csdn.net/weixin_43859562/article/details/128742139
Author: 吃再多糖也不长胖
Title: Hive sql 每天场景题37-38
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817790/
转载文章受原作者版权保护。转载请注明原作者出处！