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);
