SQL面试题2：留存率问题

引言

场景介绍：

在互联网产品运营中，用户注册量和留存率是衡量产品吸引力和用户粘性的关键指标，直接影响产品的可持续发展和商业价值。通过分析这些数据，企业可以了解用户行为，优化产品策略，提升用户体验。

题目描述：

假设有一个记录用户注册信息表 reg_tb，包含两个字段：uid（用户ID）和reg_dt（用户注册日期）。同时，还有一个记录用户登录行为表login_tb，包含两个字段：uid（用户ID）和login_dt（用户登录日期）。现在，你需要完成以下任务：

1. 计算次日留存率。即，1月1日注册的用户，1月2日登录的比例是多少。比如今天有 100 个用户注册，明天有 20 个这些注册过的用户又登录了，那次日留存率就是 20% 。这个指标能反映出产品对新用户的初步吸引力。
2. 计算三日留存率。即今天（1月1日）注册的用户，在第三天（1月4日）还会登录的比例。
3. 计算七日留存率。通过这两个指标，了解新用户经过一段时间后，对产品的持续参与度如何。

数据准备与代码实现

数据准备

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. 计算次日留存率

步骤：

1. 利用left join将 reg_tb 表和 login_tb 表进行左连接，保证 reg_tb 表中的所有记录都会出现，连接条件是uid 相等，且 login_tb 表中的登录日期 logindt 是 reg_tb 表中注册日期 regdt 加 1 天，这样可以找到在注册后第二天登录的用户。
2. 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);