Hive SQL面试题-流失回流用户数统计

这篇具有很好参考价值的文章主要介绍了Hive SQL面试题-流失回流用户数统计。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

流失回流用户数统计


根据用户最后一次登录记录表,统计每天的流失(一段时间未登录平台)用户数量,和回流用户(一段时间未登录平台,但今天重新登录了平台)数量。
执行环境:Hive on Spark

1 统计指标

从用户最后一次登录记录表中统计如下指标,当日流失用户数量当日回流用户数量

统计周期 指标 说明
最近1日 流失用户数 之前活跃过的用户,最近一段时间未活跃,就称为流失用户。
此处要求统计7日前(只包含7日前当天)活跃,但最近7日未活跃的用户总数。
最近1日 回流用户数 之前的活跃用户,一段时间未活跃(流失),今日又活跃了,就称为回流用户。
此处要求统计回流用户总数。

2 测试数据

  • 建表
-- user_id: 用户 id
-- last_login_date: 用户上次登录日期
-- dt: 日期分区
create table user_login_log (
    user_id string,
    last_login_date string,
    dt string
);
  • 插入数据
-- 1001 每天登录,普通用户
-- 1002 回流用户,一段时间未登录,然后 2020-06-14 又活跃了
-- 1003 流失用户,从 2020-06-07 之后就没有登录过了
insert into user_login_log values 
('1001', '2020-06-13', '2020-06-13'),
('1001', '2020-06-14', '2020-06-14'),
('1002', '2020-06-01', '2020-06-13'),
('1002', '2020-06-14', '2020-06-14'),
('1003', '2020-06-07', '2020-06-13'),
('1003', '2020-06-07', '2020-06-14');
  • 数据样例
select * from user_login_log;
user_id last_login_date dt
1001 2020-06-13 2020-06-13
1002 2020-06-01 2020-06-13
1003 2020-06-07 2020-06-13
1001 2020-06-14 2020-06-14
1002 2020-06-14 2020-06-14
1003 2020-06-07 2020-06-14

3 实现思路

3.1 流失用户

统计当天(2020-06-14)的流失用户,此类用户特征是上次登录日期距当天日期已经达到 7 天

select '2020-06-14' as dt,
    count(*) as user_lost_count
from user_login_log where dt = '2020-06-14'
    and last_login_date = date_sub('2020-06-14', 7);
3.2 回流用户

统计当天(2020-06-14)的回流用户,此类用户的特征为当天(2020-06-14)登陆了平台,同时昨天的还属于流失用户,即前一天(2020-06-13)的最后登录日期在7天之前文章来源地址https://www.toymoban.com/news/detail-403998.html

  • 取出 2020-06-14 登录的用户
select user_id from user_login_log 
where dt = '2020-06-14' 
and last_login_date = '2020-06-14';
  • 取出 2020-06-13 还属于流失用户的用户
-- 此处 last_login_date <= date_sub('2020-06-14', 8) 
-- 表示前一天 date_sub('2020-06-14', 1) 处在流失状态的用户
select user_id from user_login_log
where dt = date_sub('2020-06-14', 1) 
and last_login_date <= date_sub('2020-06-14', 8);
  • 将两份数据 join 起来获得流失用户数
with t1 as (
    select user_id from user_login_log 
    where dt = '2020-06-14' 
    and last_login_date = '2020-06-14'
),t2 as (
    select user_id from user_login_log
    where dt = date_sub('2020-06-14', 1) 
    and last_login_date <= date_sub('2020-06-14', 8)
) select '2020-06-14',count(distinct t1.user_id) 
from t1 inner join t2 on t1.user_id = t2.user_id;

到了这里,关于Hive SQL面试题-流失回流用户数统计的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 商城的TPS与并发用户数是如何换算的?请编写商城的性能测试用例?

    商城的TPS与并发用户数的换算关系可以通过以下公式计算: TPS = 并发用户数 / 平均事务响应时间 其中,平均事务响应时间是指系统处理一个事务所需的平均时间。 下面是商城性能测试的一些用例示例: 用户登录: 目标:测试用户登录功能的性能和稳定性。 测试步骤:模拟

    2024年02月12日
    浏览(36)
  • 华为余承东表示「鸿蒙 HarmonyOS 2 升级用户数突破 1 亿」,意味着谷歌已被吓得瑟瑟发抖...

    本来全球手机市场有超过八成的份额为安卓占有,而苹果占有一成多。苹果每年的手机销量大约在2亿多部,如果鸿蒙系统每年增加2亿用户,那么鸿蒙系统就将如苹果iOS系统一样占有一成多的市场份额,这一成多市场份额正是从谷歌手里夺取的,那就代表着谷歌的安卓占有的

    2024年02月11日
    浏览(45)
  • 【性能测试】运维测试01之性能测试整体认知包括:TPS、请求响应时间、事务响应时间、并发用户数、吞吐量、吞吐率、点击率、资源使用率等性能指标详细介绍

    性能测试整体认知包括:TPS、请求响应时间、事务响应时间、并发用户数、吞吐量、吞吐率、点击率、资源使用率。 1.1 需求一 1.熟悉Linux、windows等操作系统,熟悉shell脚本; ⒉.熟悉jvm调优, tomcat调优等基础策略 3.熟悉mysq数据库,熟练掌握javascript、java、python、groovy等至少一门

    2024年02月16日
    浏览(41)
  • 【Hive SQL 每日一题】统计用户连续下单的日期区间

    测试数据 需求说明 统计用户连续下单的日期区间,所以连续的下单日期必须 = 2 ,例如: 2023-01-01,2023-01-02 。 分析步骤如下: 按 user_id 、 order_date 进行分组,同天的下单日期只保留一条。 使用 row_number 窗口函数对行号进行标记。 使用 date_sub 函数与行号标记进行运算,如果

    2024年02月09日
    浏览(36)
  • sql高频面试题-连续完成两个指定动作的用户统计

    业务背景 某购物APP最近上线了一个新功能,用户签到后可以跳转到大转盘抽奖,抽奖获得的奖金可以抵消购物的费用,以此来培养用户使用app的习惯。 数据表介绍 现有一张用户行为表action_log,主要字段如下,记录了用户在app上的所有行为日志,即何人userid在何时action_time进

    2024年02月13日
    浏览(44)
  • SQL 经典面试题:统计最近七天连续三天活跃的用户

    给定 mid,dt 的用户登录记录表,查找最近 7 天内连续 3 天活跃的用户 id tmp_table.tmp_login_test 实现思路:获取最近 7 天的用户登录记录数据(在 where 中限定),对数据进行 rank 排序,计算登录日期与 rank 值之间的差值(使用 date_sub 函数)得到一个差值日期,如果登录日期是连

    2024年02月12日
    浏览(41)
  • 这些年写过的花式sql - 第3句 今日流失用户

    第3句 今日流失用户 需求: 当日流失用户的定义:昨天登录的,今天没登录的用户数 有一张用户登录日志表,有字段 date_stamp(日期时间戳),用户id(uid)。如果用户在某天登录了,该表会有一条记录。 解析: a 表和b表的连接条件是 uid相同 且时间戳相差一天,a 即前一天,

    2024年02月14日
    浏览(35)
  • SQL统计连续登陆3天的用户(连续活跃超3天用户)

    1. 数据准备 2. 方法一: 差值计算 user_id active_date rn 10001 2023-02-01 1 10001 2023-02-03 2 10001 2023-02-04 3 10001 2023-02-05 4 10002 2023-02-02 1 10002 2023-02-03 2 10002 2023-02-04 3 10002 2023-02-05 4 10002 2023-02-07 5 … … … user_id active_date rn sub_date 10001 2023-02-01 1 2023-01-31 10001 2023-02-03 2 2023-02-01 10001 2023-02-04 3

    2024年04月25日
    浏览(30)
  • 【前端面试手册】CSS系列-回流与重构

    本专栏收录于前端面试手册-CSS系列如果该文章对您有帮助还希望你能点一个小小的订阅,来增加博主创作的动力✍🏻话不多说开始进入正题 回流和重构在前端CSS中是一个常见的问题,那么你对回流何重构有哪些了解呢? 一、什么场景下会触发回流和重构,如何减少回流重构

    2024年02月11日
    浏览(56)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包