SQL面试题2:留存率问题
引言
场景介绍:
在互联网产品运营中,用户注册量和留存率是衡量产品吸引力和用户粘性的关键指标,直接影响产品的可持续发展和商业价值。通过分析这些数据,企业可以了解用户行为,优化产品策略,提升用户体验。
题目描述:
假设有一个记录用户注册信息表 reg_tb,包含两个字段:uid(用户ID)和reg_dt(用户注册日期)。同时,还有一个记录用户登录行为表login_tb,包含两个字段:uid(用户ID)和login_dt(用户登录日期)。现在,你需要完成以下任务:
- 计算次日留存率。即,1月1日注册的用户,1月2日登录的比例是多少。比如今天有 100 个用户注册,明天有 20 个这些注册过的用户又登录了,那次日留存率就是 20% 。这个指标能反映出产品对新用户的初步吸引力。
- 计算三日留存率。即今天(1月1日)注册的用户,在第三天(1月4日)还会登录的比例。
- 计算七日留存率。通过这两个指标,了解新用户经过一段时间后,对产品的持续参与度如何。
数据准备与代码实现
数据准备
CREATE TABLE reg_tb (uid INT,regdt DATE
);INSERT INTO reg_tb VALUES
(1, '2025-01-01'),
(2, '2025-01-01'),
(3, '2025-01-02'),
(4, '2025-01-02'),
(5, '2025-01-02');
CREATE TABLE login_tb (uid INT,logindt DATE
);INSERT INTO login_tb VALUES
(1, '2025-01-02'),
(1, '2025-01-03'),
(2, '2025-01-03'),
(2, '2025-01-04'),
(3, '2025-01-03'),
(3, '2025-01-05'),
(4, '2025-01-05'),
(5, '2025-01-09');
如图所示,红色为次日登录,黄色为第三日登录,紫色为第七日登录。

1. 计算次日留存率
步骤:
- 利用
left join将 reg_tb 表和 login_tb 表进行左连接,保证 reg_tb 表中的所有记录都会出现,连接条件是uid 相等,且 login_tb 表中的登录日期 logindt 是 reg_tb 表中注册日期 regdt 加 1 天,这样可以找到在注册后第二天登录的用户。 COUNT(DISTINCT l.uid)统计在login_tb表中与reg_tb表通过LEFT JOIN连接后满足条件的不重复的uid的数量。COUNT(DISTINCT r.uid):统计reg_tb表中的不重复的uid的数量,两者相除计算次日留存率。
注意点:由于count()在left join情况下,当没有匹配的登录记录时,l.uid 会是 NULL ,但 count() 会将其作为计数的一部分。因此,应使用count(distinct l.uid)代替count(l.uid)使得结果更加准确。
select count(distinct l.uid)/count(distinct r.uid) rrd1
from reg_tb r
left join login_tb l on r.uid = l.uid and l.logindt = date_add(r.regdt,1);
优化:将结果以百分比的形式显示四舍五入到一位小数
select ROUND(COUNT(DISTINCT l.uid) / COUNT(DISTINCT r.uid) * 100, 1) || '%' AS rrd1
from reg_tb r
left join login_tb l on r.uid = l.uid and l.logindt = date_add(r.regdt,1);
2. 次日、三日、七日留存率
select ROUND(COUNT(DISTINCT l1.uid) / COUNT(DISTINCT r.uid) * 100, 1) || '%' AS rrd1,ROUND(COUNT(DISTINCT l3.uid) / COUNT(DISTINCT r.uid) * 100, 1) || '%' AS rrd3,ROUND(COUNT(DISTINCT l7.uid) / COUNT(DISTINCT r.uid) * 100, 1) || '%' AS rrd7
from reg_tb r
left join login_tb l1 on r.uid = l1.uid and l1.logindt = date_add(r.regdt,1)
left join login_tb l3 on r.uid = l3.uid and l3.logindt = date_add(r.regdt,3)
left join login_tb l7 on r.uid = l7.uid and l7.logindt = date_add(r.regdt,7);

相关文章:
SQL面试题2:留存率问题
引言 场景介绍: 在互联网产品运营中,用户注册量和留存率是衡量产品吸引力和用户粘性的关键指标,直接影响产品的可持续发展和商业价值。通过分析这些数据,企业可以了解用户行为,优化产品策略,提升用户体验…...
晨辉面试抽签和评分管理系统之九:随机编排考生的分组(以教师资格考试面试为例)
晨辉面试抽签和评分管理系统(下载地址:www.chenhuisoft.cn)是公务员招录面试、教师资格考试面试、企业招录面试等各类面试通用的考生编排、考生入场抽签、候考室倒计时管理、面试考官抽签、面试评分记录和成绩核算的面试全流程信息化管理软件。提供了考生…...
【EtherCATBridge】- KRTS C++示例精讲(9)
EtherCATBridge示例讲解 文章目录 EtherCATBridge示例讲解结构说明代码说明 项目打开请查看【BaseFunction精讲】。 结构说明 EtherCATBridge.h :数据定义 EtherCATBridge.cpp :应用层源码 EtherCATBridge_dll.cpp :内核层源码 其余文件说明…...
C++实现设计模式--- 观察者模式 (Observer)
观察者模式 (Observer) 观察者模式 是一种行为型设计模式,它定义了一种一对多的依赖关系,使得当一个对象的状态发生改变时,其依赖者(观察者)会收到通知并自动更新。 意图 定义对象之间的一对多依赖关系。当一个对象状…...
iOS 解决两个tableView.嵌套滚动手势冲突
我们有这样一个场景,就是页面上有一个大的tableView, 每一个cell都是和屏幕一样高的,然后cell中还有一个可以 tableView,比如直播间的情形,这个时候如果我们拖动 cell里面的tableView滚动的话,如果滚动到内…...
Lianwei 安全周报|2025.1.13
新的一周又开始了,以下是本周「Lianwei周报」,我们总结推荐了本周的政策/标准/指南最新动态、热点资讯和安全事件,保证大家不错过本周的每一个重点! 政策/标准/指南最新动态 01 美国国土安全部发布《公共部门生成式人工智能部署手…...
rtthread学习笔记系列(2) -- 宏
文章目录 2.链接文件2.0. 参考链接2.1._stext 和 _etext2.2. "."与"*符号作用2.3..linkonce 段2.4. KEEP2.5 ENTRY2.6 PROVIDE2.7 AT2.8 SORT2.9 NOLOAD 源文件路径:https://github.com/wdfk-prog/RT-Thread-Study 2.链接文件 2.0. 参考链接 https://home.cs…...
美摄科技PC端视频编辑解决方案,为企业打造专属的高效创作平台
在当今这个信息爆炸的时代,视频已成为不可或缺的重要内容形式,美摄科技推出了PC端视频编辑解决方案的私有化部署服务,旨在为企业提供一款量身定制的高效创作平台。 一、全面功能,满足企业多样化需求 美摄科技的PC端视频编辑解决…...
服务端开发模式-thinkphp-重新整理workman
一、登录接口 <?php /*** 登录退出操作* User: 龙哥三年风水* Date: 2024/10/29* Time: 15:53*/ namespace app\controller\common; use app\controller\Emptys; use app\model\permission\Admin; use app\model\param\System as SystemModel; use Email\EmailSender; use…...
HTB:Access[WriteUP]
目录 连接至HTB服务器并启动靶机 信息收集 使用rustscan对靶机TCP端口进行开放扫描 将靶机TCP开放端口号提取并保存 使用nmap对靶机TCP开放端口进行脚本、服务扫描 使用nmap对靶机TCP开放端口进行漏洞、系统扫描 使用nmap对靶机常用UDP端口进行开放扫描 尝试匿名连接至…...
【论文笔记】SmileSplat:稀疏视角+pose-free+泛化
还是一篇基于dust3r的稀疏视角重建工作,作者联合优化了相机内外参与GS模型,实验结果表明优于noposplat。 abstract 在本文中,提出了一种新颖的可泛化高斯方法 SmileSplat,可以对无约束(未标定相机的)稀疏多…...
电机控制的数字化升级:基于DSP和FPGA的仿真与实现
数字信号处理器(DSP,Digital Signal Processor)在工业自动化领域的应用日益广泛。DSP是一种专门用于将模拟信号转换成数字信号并进行处理的技术,能够实现信号的数字滤波、重构、调制和解调等多项功能,确保信号处理的精…...
1/14 C++
练习:将图形类的获取周长和获取面积函数设置成虚函数,完成多态 再定义一个全局函数,能够在该函数中实现:无论传递任何图形,都可以输出传递的图形的周长和面积 #include <iostream>using namespace std; class Sh…...
java springboot3.x jwt+spring security6.x实现用户登录认证
springboot3.x jwtspring security6.x实现用户登录认证 什么是JWT JWT(JSON Web Token)是一种开放标准(RFC 7519),它用于在网络应用环境中传递声明。通常,JWT用于身份验证和信息交换。JWT的一个典型用法是…...
YOLOv5训练长方形图像详解
文章目录 YOLOv5训练长方形图像详解一、引言二、数据集准备1、创建文件夹结构2、标注图像3、生成标注文件 三、配置文件1、创建数据集配置文件2、选择模型配置文件 四、训练模型1、修改训练参数2、开始训练 五、使用示例1、测试模型2、评估模型 六、总结 YOLOv5训练长方形图像详…...
【2025最新】Poe保姆级订阅指南,Poe订阅看这一篇就够了!最方便使用各类AI!
1.Poe是什么? Poe, 全称Platform for Open Exploration。 Poe本身并不提供基础的大语言模型,而是整合多个来自不同科技巨头的基于不同模型的AI聊天机器人,其中包括来自OpenAI的ChatGPT,Anthropic的Claude、Google的PaLM…...
type1-100,2 words
dish n.餐具、碟,盘子;菜肴、饭菜(指一顿餐食中的一道菜) kind of 稍微;有点 sort of 稍微;有点儿 smallish adj.有点小的 crack 敲碎/裂,敲开,砸开,砸碎;裂开…...
Leetcode 377. 组合总和 Ⅳ 动态规划
原题链接:Leetcode 377. 组合总和 Ⅳ 可参考官解 class Solution { public:int combinationSum4(vector<int>& nums, int target) {vector<int> dp(target 1);dp[0] 1;// 总和为 i 的元素组合的个数for (int i 1; i < target; i) {// 每次都…...
计算机网络(五)——传输层
一、功能 传输层的主要功能是向两台主机进程之间的通信提供通用的数据传输服务。功能包括实现端到端的通信、多路复用和多路分用、差错控制、流量控制等。 复用:多个应用进程可以通过同一个传输层发送数据。 分用:传输层在接收数据后可以将这些数据正确分…...
【SQL】进阶知识 -- 删除表的几种方法(包含表内单个字段的删除方法)
大家好!欢迎来到本篇SQL进阶博客。如果你已经掌握了基础的SQL操作,接下来就让我们一起探索删除表的几种方法。删除表可能听起来有点危险,事实也是如此,所以在我们实际开发过程中,大多数时候我们都有数据的使用权限&…...
国防科技大学计算机基础课程笔记02信息编码
1.机内码和国标码 国标码就是我们非常熟悉的这个GB2312,但是因为都是16进制,因此这个了16进制的数据既可以翻译成为这个机器码,也可以翻译成为这个国标码,所以这个时候很容易会出现这个歧义的情况; 因此,我们的这个国…...
idea大量爆红问题解决
问题描述 在学习和工作中,idea是程序员不可缺少的一个工具,但是突然在有些时候就会出现大量爆红的问题,发现无法跳转,无论是关机重启或者是替换root都无法解决 就是如上所展示的问题,但是程序依然可以启动。 问题解决…...
【力扣数据库知识手册笔记】索引
索引 索引的优缺点 优点1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。2. 可以加快数据的检索速度(创建索引的主要原因)。3. 可以加速表和表之间的连接,实现数据的参考完整性。4. 可以在查询过程中,…...
【Java学习笔记】Arrays类
Arrays 类 1. 导入包:import java.util.Arrays 2. 常用方法一览表 方法描述Arrays.toString()返回数组的字符串形式Arrays.sort()排序(自然排序和定制排序)Arrays.binarySearch()通过二分搜索法进行查找(前提:数组是…...
Vue3 + Element Plus + TypeScript中el-transfer穿梭框组件使用详解及示例
使用详解 Element Plus 的 el-transfer 组件是一个强大的穿梭框组件,常用于在两个集合之间进行数据转移,如权限分配、数据选择等场景。下面我将详细介绍其用法并提供一个完整示例。 核心特性与用法 基本属性 v-model:绑定右侧列表的值&…...
DAY 47
三、通道注意力 3.1 通道注意力的定义 # 新增:通道注意力模块(SE模块) class ChannelAttention(nn.Module):"""通道注意力模块(Squeeze-and-Excitation)"""def __init__(self, in_channels, reduction_rat…...
定时器任务——若依源码分析
分析util包下面的工具类schedule utils: ScheduleUtils 是若依中用于与 Quartz 框架交互的工具类,封装了定时任务的 创建、更新、暂停、删除等核心逻辑。 createScheduleJob createScheduleJob 用于将任务注册到 Quartz,先构建任务的 JobD…...
高等数学(下)题型笔记(八)空间解析几何与向量代数
目录 0 前言 1 向量的点乘 1.1 基本公式 1.2 例题 2 向量的叉乘 2.1 基础知识 2.2 例题 3 空间平面方程 3.1 基础知识 3.2 例题 4 空间直线方程 4.1 基础知识 4.2 例题 5 旋转曲面及其方程 5.1 基础知识 5.2 例题 6 空间曲面的法线与切平面 6.1 基础知识 6.2…...
微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据
微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据 Power Query 具有大量专门帮助您清理和准备数据以供分析的功能。 您将了解如何简化复杂模型、更改数据类型、重命名对象和透视数据。 您还将了解如何分析列,以便知晓哪些列包含有价值的数据,…...
以光量子为例,详解量子获取方式
光量子技术获取量子比特可在室温下进行。该方式有望通过与名为硅光子学(silicon photonics)的光波导(optical waveguide)芯片制造技术和光纤等光通信技术相结合来实现量子计算机。量子力学中,光既是波又是粒子。光子本…...
