SQL 经典面试题:统计最近七天连续三天活跃的用户

这篇具有很好参考价值的文章主要介绍了SQL 经典面试题:统计最近七天连续三天活跃的用户。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

1 需求

给定 mid,dt 的用户登录记录表,查找最近 7 天内连续 3 天活跃的用户 id

2 数据表

tmp_table.tmp_login_test

CREATE TABLE tmp_table.tmp_login_test (
    mid string,
    dt string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
insert into tmp_table.tmp_login_test values
("zhangsan","2021-08-01"),
("zhangsan","2021-08-02"),
("zhangsan","2021-08-04"),
("zhangsan","2021-08-05"),
("zhangsan","2021-08-06"),
("zhangsan","2021-08-08"),
("zhangsan","2021-08-09"),
("zhangsan","2021-08-10"),
("lisi","2021-08-01"),
("lisi","2021-08-02"),
("lisi","2021-08-04"),
("lisi","2021-08-05"),
("lisi","2021-08-08"),
("lisi","2021-08-10"),
("wangwu","2021-08-01"),
("wangwu","2021-08-04"),
("wangwu","2021-08-07"),
("zhaoliu","2021-08-01"),
("zhaoliu","2021-08-02"),
("zhaoliu","2021-08-03"),
("zhaoliu","2021-08-04"),
("zhaoliu","2021-08-05"),
("zhaoliu","2021-08-06"),
("zhaoliu","2021-08-07"),
("zhaoliu","2021-08-08"),
("zhaoliu","2021-08-09"),
("zhaoliu","2021-08-10");

3 Sql 实现

实现思路:获取最近 7 天的用户登录记录数据(在 where 中限定),对数据进行 rank 排序,计算登录日期与 rank 值之间的差值(使用 date_sub 函数)得到一个差值日期,如果登录日期是连续的那么计算得到的差值日期是同一个,在此基础上基于用户,差值日期分组,统计 dt 的去重数量,即可得到每个用户每次连续登录的天数。在本例中,需要统计 7 天内连续 3 天登录的用户,所以只需要取出连续登录天数大于等于 3 的 uid 即完成需求。文章来源地址https://www.toymoban.com/news/detail-519750.html

  • 对用户的登录行为按 mid 分组,组内按登录日期进行排序
select 
    mid, dt, 
    rank() over(partition by mid order by dt) rank_mid_dt 
from tmp_table.tmp_login_test 
where dt >= date_sub('2021-08-10', 6) and dt <= '2021-08-10';
+-----------+-------------+--------------+
|    mid    |     dt      | rank_mid_dt  |
+-----------+-------------+--------------+
| lisi      | 2021-08-04  | 1            |
| lisi      | 2021-08-05  | 2            |
| lisi      | 2021-08-08  | 3            |
| lisi      | 2021-08-10  | 4            |
| zhangsan  | 2021-08-04  | 1            |
| zhangsan  | 2021-08-05  | 2            |
| zhangsan  | 2021-08-06  | 3            |
| zhangsan  | 2021-08-08  | 4            |
| zhangsan  | 2021-08-09  | 5            |
| zhangsan  | 2021-08-10  | 6            |
| zhaoliu   | 2021-08-04  | 1            |
| zhaoliu   | 2021-08-05  | 2            |
| zhaoliu   | 2021-08-06  | 3            |
| zhaoliu   | 2021-08-07  | 4            |
| zhaoliu   | 2021-08-08  | 5            |
| zhaoliu   | 2021-08-09  | 6            |
| zhaoliu   | 2021-08-10  | 7            |
| wangwu    | 2021-08-04  | 1            |
| wangwu    | 2021-08-07  | 2            |
+-----------+-------------+--------------+
  • 基于上表计算 dt 和 rank_mid_dt 日期差
select 
    mid, dt, rank_mid_dt, 
    date_sub(dt, rank_mid_dt) date_diff 
from (select mid, dt, rank() over(partition by mid order by dt) rank_mid_dt from tmp_table.tmp_login_test where dt >= date_sub('2021-08-10', 6) and dt <= '2021-08-10') t1;
+-----------+-------------+--------------+-------------+
|    mid    |     dt      | rank_mid_dt  |  date_diff  |
+-----------+-------------+--------------+-------------+
| lisi      | 2021-08-04  | 1            | 2021-08-03  |
| lisi      | 2021-08-05  | 2            | 2021-08-03  |
| lisi      | 2021-08-08  | 3            | 2021-08-05  |
| lisi      | 2021-08-10  | 4            | 2021-08-06  |
| zhangsan  | 2021-08-04  | 1            | 2021-08-03  |
| zhangsan  | 2021-08-05  | 2            | 2021-08-03  |
| zhangsan  | 2021-08-06  | 3            | 2021-08-03  |
| zhangsan  | 2021-08-08  | 4            | 2021-08-04  |
| zhangsan  | 2021-08-09  | 5            | 2021-08-04  |
| zhangsan  | 2021-08-10  | 6            | 2021-08-04  |
| zhaoliu   | 2021-08-04  | 1            | 2021-08-03  |
| zhaoliu   | 2021-08-05  | 2            | 2021-08-03  |
| zhaoliu   | 2021-08-06  | 3            | 2021-08-03  |
| zhaoliu   | 2021-08-07  | 4            | 2021-08-03  |
| zhaoliu   | 2021-08-08  | 5            | 2021-08-03  |
| zhaoliu   | 2021-08-09  | 6            | 2021-08-03  |
| zhaoliu   | 2021-08-10  | 7            | 2021-08-03  |
| wangwu    | 2021-08-04  | 1            | 2021-08-03  |
| wangwu    | 2021-08-07  | 2            | 2021-08-05  |
+-----------+-------------+--------------+-------------+
  • 基于 mid,date_diff 分组,统计 dt 的去重数量,并取出数量大于 3 的
select 
    mid, date_diff, 
    count(distinct dt) cnt 
from (select mid, dt, rank_mid_dt, date_sub(dt, rank_mid_dt) date_diff from (select mid, dt, rank() over(partition by mid order by dt) rank_mid_dt from tmp_table.tmp_login_test where dt >= date_sub('2021-08-10', 6) and dt <= '2021-08-10') t1) t2 
group by mid, date_diff having count(distinct dt) >= 3;
+-----------+-------------+------+
|    mid    |  date_diff  | cnt  |
+-----------+-------------+------+
| zhaoliu   | 2021-08-03  | 7    |
| zhangsan  | 2021-08-03  | 3    |
| zhangsan  | 2021-08-04  | 3    |
+-----------+-------------+------+
  • 取出 mid
select 
    distinct mid
from (select mid, date_diff, count(distinct dt) cnt from (select mid, dt, rank_mid_dt, date_sub(dt, rank_mid_dt) date_diff from (select mid, dt, rank() over(partition by mid order by dt) rank_mid_dt from tmp_table.tmp_login_test where dt >= date_sub('2021-08-10', 6) and dt <= '2021-08-10') t1) t2 
group by mid, date_diff having count(distinct dt) >= 3) t3;
+-----------+
|    mid    |
+-----------+
| zhangsan  |
| zhaoliu   |
+-----------+

到了这里,关于SQL 经典面试题:统计最近七天连续三天活跃的用户的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • SQL-01求连续七天登陆的用户

    2024年01月18日
    浏览(44)
  • 「SQL面试题库」 No_66 查询近30天活跃用户数

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

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

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

    2024年02月09日
    浏览(36)
  • 七天学会C语言-第三天(循环语句)

    使用 while 语句,您可以创建一个循环,它会重复执行一段代码,直到指定的条件不再满足。 例 1: 求 2+4+6+···+100。 这个示例通过 while 循环计算了2到100的偶数之和。 运行结果: 例 2: 求 2×4×6×8×···×50。 这个示例使用 while 循环计算了2到50的偶数的乘积。 运行结果: 例

    2024年02月07日
    浏览(36)
  • 「SQL面试题库」 No_38 平面上的最近距离

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2023年04月24日
    浏览(47)
  • 「SQL面试题库」 No_39 直线上的最近距离

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2023年04月20日
    浏览(50)
  • 「SQL面试题库」 No_34 连续空余座位

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2023年04月22日
    浏览(63)
  • 微信小程序实现连续签到七天

    签到满了七天之后,签到第八天会回到第一天重新开始签到 断签之后会从第一天重新开始

    2024年02月09日
    浏览(49)
  • 「SQL面试题库」 No_88 找到连续区间的开始和结束数字

    「SQL面试题库」是由 不是西红柿 发起,全员免费参与的SQL学习活动。我每天发布1道SQL面试真题,从简单到困难,涵盖所有SQL知识点,我敢保证只要做完这100道题,不仅能轻松搞定面试,代码能力和工作效率也会有明显提升。 1.1 活动流程 整理题目 :西红柿每天无论刮风下雨

    2024年02月08日
    浏览(43)
  • 【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2024年02月02日
    浏览(72)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包