拼多多大数据面试SQL-留存率计算
一、题目
手机中的相机是深受大家喜爱的应用之一。现有表t5_user_behavior 用户行为表，包含用户ID，应用名称，时长和打开次数，某手机厂商想要分析「相机」应用的活跃情况，需统计每天的：
- 活跃用户数
- 次日留存数 / 次日留存率
- 3日留存数 / 3日留存率
- 7日留存数 / 7日留存率
+----------+-----------+-----------+---------------+-------------+
| user_id | app_name | duration | launch_count | login_date |
+----------+-----------+-----------+---------------+-------------+
| 01 | 相机 | 1 | 2 | 2026-05-01 |
| 01 | 相机 | 2 | 1 | 2026-05-02 |
| 01 | 相机 | 3 | 2 | 2026-05-04 |
| 02 | 微信 | 2 | 3 | 2026-05-02 |
| 03 | 大众点评 | 4 | 2 | 2026-05-03 |
| 04 | 微信 | 6 | 3 | 2026-05-01 |
| 05 | 相机 | 3 | 1 | 2026-05-03 |
| 05 | 相机 | 2 | 2 | 2026-05-04 |
| 06 | 相机 | 2 | 3 | 2026-05-01 |
| 06 | 相机 | 1 | 2 | 2026-05-02 |
| 06 | 相机 | 2 | 1 | 2026-05-08 |
| 07 | 相机 | 2 | 2 | 2026-05-02 |
| 07 | 相机 | 3 | 1 | 2026-05-03 |
| 07 | 相机 | 1 | 2 | 2026-05-05 |
| 08 | 微信 | 1 | 1 | 2026-05-01 |
| 09 | 大众点评 | 3 | 2 | 2026-05-02 |
| 10 | 相机 | 4 | 3 | 2026-05-03 |
| 11 | 相机 | 5 | 4 | 2026-05-02 |
| 11 | 相机 | 2 | 3 | 2026-05-03 |
| 12 | 大众点评 | 6 | 5 | 2026-05-01 |
| 14 | 相机 | 2 | 2 | 2026-05-03 |
| 15 | 相机 | 1 | 3 | 2026-05-01 |
| 15 | 相机 | 2 | 2 | 2026-05-04 |
+----------+-----------+-----------+---------------+-------------+
23 rows selected (0.34 seconds)
二、分析
比较常见的留存分析，与新增留存存在差别的是，只有有过活跃，要分析其后续的留存情况。本题解法很多，给出集中不同的解法供大家参考
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
解法一：自联结
将表自联结，计算同一用户两次登录的天数差，再按日期聚合统计。
执行SQL
SELECT a.login_date,
COUNT(DISTINCT a.user_id) AS `活跃用户数`,
COUNT(DISTINCT CASE
WHEN DATEDIFF(b.login_date, a.login_date) = 1
THEN a.user_id END) AS `次日留存数`,
round(COUNT(DISTINCT CASE WHEN DATEDIFF(b.login_date, a.login_date) = 1 THEN a.user_id END)
/ COUNT(DISTINCT a.user_id), 2) AS `次日留存率`,
COUNT(DISTINCT CASE
WHEN DATEDIFF(b.login_date, a.login_date) = 3
THEN a.user_id END) AS `3日留存数`,
round(COUNT(DISTINCT CASE WHEN DATEDIFF(b.login_date, a.login_date) = 3 THEN a.user_id END)
/ COUNT(DISTINCT a.user_id), 2) AS `3日留存率`,
COUNT(DISTINCT CASE
WHEN DATEDIFF(b.login_date, a.login_date) = 7
THEN a.user_id END) AS `7日留存数`,
round(COUNT(DISTINCT CASE WHEN DATEDIFF(b.login_date, a.login_date) = 7 THEN a.user_id END)
/ COUNT(DISTINCT a.user_id), 2) AS `7 日留存率`
FROM (select *
from t5_user_behavior
where app_name = '相机') a
left join
(select *
from t5_user_behavior
where app_name = '相机') b
on a.user_id = b.user_id
group by a.login_date
order by a.login_date
;
执行结果
+-------------+--------+--------+--------+--------+--------+--------+---------+
| login_date | 活跃用户数 | 次日留存数 | 次日留存率 | 3日留存数 | 3日留存率 | 7日留存数 | 7 日留存率 |
+-------------+--------+--------+--------+--------+--------+--------+---------+
| 2026-05-01 | 3 | 2 | 0.67 | 2 | 0.67 | 1 | 0.33 |
| 2026-05-02 | 4 | 2 | 0.5 | 1 | 0.25 | 0 | 0.0 |
| 2026-05-03 | 5 | 1 | 0.2 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-04 | 3 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-05 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-08 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
+-------------+--------+--------+--------+--------+--------+--------+---------+
6 rows selected (0.761 seconds)
解法二：多次自联结，日期精确匹配
多次join，但是日期精确匹配
执行SQL
WITH camera AS (SELECT DISTINCT user_id, login_date
FROM t5_user_behavior
WHERE app_name = '相机')
SELECT a.login_date,
COUNT(DISTINCT a.user_id) AS `活跃用户数`,
COUNT(DISTINCT b1.user_id) AS `次日留存数`,
round(COUNT(DISTINCT b1.user_id) / COUNT(DISTINCT a.user_id), 2) AS `次日留存率`,
COUNT(DISTINCT b3.user_id) AS `3日留存数`,
round(COUNT(DISTINCT b3.user_id) / COUNT(DISTINCT a.user_id), 2) AS `3日留存率`,
COUNT(DISTINCT b7.user_id) AS `7日留存数`,
round(COUNT(DISTINCT b7.user_id) / COUNT(DISTINCT a.user_id), 2) AS `7日留存率`
FROM camera a
LEFT JOIN camera b1
ON a.user_id = b1.user_id
AND b1.login_date = DATE_ADD(a.login_date, 1)
LEFT JOIN camera b3
ON a.user_id = b3.user_id
AND b3.login_date = DATE_ADD(a.login_date, 3)
LEFT JOIN camera b7
ON a.user_id = b7.user_id
AND b7.login_date = DATE_ADD(a.login_date, 7)
GROUP BY a.login_date
ORDER BY a.login_date;
执行结果
+-------------+--------+--------+--------+--------+--------+--------+--------+
| login_date | 活跃用户数 | 次日留存数 | 次日留存率 | 3日留存数 | 3日留存率 | 7日留存数 | 7日留存率 |
+-------------+--------+--------+--------+--------+--------+--------+--------+
| 2026-05-01 | 3 | 2 | 0.67 | 2 | 0.67 | 1 | 0.33 |
| 2026-05-02 | 4 | 2 | 0.5 | 1 | 0.25 | 0 | 0.0 |
| 2026-05-03 | 5 | 1 | 0.2 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-04 | 3 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-05 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-08 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
+-------------+--------+--------+--------+--------+--------+--------+--------+
解法三、开窗函数，使用值控制窗口大小
由于spark 的range 不支持日期格式，仅支持数值类型，所以把日期改为了日期时间戳； 我们也可以随便找个日期改成日期差；
执行SQL
WITH camera AS (SELECT DISTINCT user_id,
login_date,
-- 将日期转为 Unix 时间戳
unix_timestamp(cast(login_date as timestamp)) AS login_ts
FROM t5_user_behavior
WHERE app_name = '相机'),
flagged AS (SELECT user_id,
login_date,
-- 1天后：86400秒
COUNT(*) OVER (
PARTITION BY user_id
ORDER BY login_ts
RANGE BETWEEN 86400 FOLLOWING AND 86400 FOLLOWING
) AS has_day1,
-- 3天后：259200秒
COUNT(*) OVER (
PARTITION BY user_id
ORDER BY login_ts
RANGE BETWEEN 259200 FOLLOWING AND 259200 FOLLOWING
) AS has_day3,
-- 7天后：604800秒
COUNT(*) OVER (
PARTITION BY user_id
ORDER BY login_ts
RANGE BETWEEN 604800 FOLLOWING AND 604800 FOLLOWING
) AS has_day7
FROM camera),
metrics AS (SELECT login_date,
COUNT(DISTINCT user_id) AS active_cnt,
COUNT(DISTINCT CASE WHEN has_day1 > 0 THEN user_id END) AS day1_cnt,
COUNT(DISTINCT CASE WHEN has_day3 > 0 THEN user_id END) AS day3_cnt,
COUNT(DISTINCT CASE WHEN has_day7 > 0 THEN user_id END) AS day7_cnt
FROM flagged
GROUP BY login_date)
SELECT login_date,
active_cnt AS `活跃用户数`,
day1_cnt AS `次日留存数`,
IF(active_cnt = 0, 0, round(day1_cnt / active_cnt, 2)) AS `次日留存率`,
day3_cnt AS `3日留存数`,
IF(active_cnt = 0, 0, round(day3_cnt / active_cnt, 2)) AS `3日留存率`,
day7_cnt AS `7日留存数`,
IF(active_cnt = 0, 0, round(day7_cnt / active_cnt, 2)) AS `7日留存率`
FROM metrics
ORDER BY login_date;
执行结果
+-------------+--------+--------+--------+--------+--------+--------+--------+
| login_date | 活跃用户数 | 次日留存数 | 次日留存率 | 3日留存数 | 3日留存率 | 7日留存数 | 7日留存率 |
+-------------+--------+--------+--------+--------+--------+--------+--------+
| 2026-05-01 | 3 | 2 | 0.67 | 2 | 0.67 | 1 | 0.33 |
| 2026-05-02 | 4 | 2 | 0.5 | 1 | 0.25 | 0 | 0.0 |
| 2026-05-03 | 5 | 1 | 0.2 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-04 | 3 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-05 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
| 2026-05-08 | 1 | 0 | 0.0 | 0 | 0.0 | 0 | 0.0 |
+-------------+--------+--------+--------+--------+--------+--------+--------+
6 rows selected (0.901 seconds)
四、数据准备
--建表语句
CREATE TABLE t5_user_behavior (
user_id String,
app_name String,
duration INT,
launch_count INT,
login_date String
);
--数据插入语句
INSERT INTO t5_user_behavior VALUES
('01', '相机', 1, 2, '2026-05-01'),
('01', '相机', 2, 1, '2026-05-02'),
('01', '相机', 3, 2, '2026-05-04'),
('06', '相机', 2, 3, '2026-05-01'),
('06', '相机', 1, 2, '2026-05-02'),
('06', '相机', 2, 1, '2026-05-08'),
('15', '相机', 1, 3, '2026-05-01'),
('15', '相机', 2, 2, '2026-05-04'),
('07', '相机', 2, 2, '2026-05-02'),
('07', '相机', 3, 1, '2026-05-03'),
('07', '相机', 1, 2, '2026-05-05'),
('11', '相机', 5, 4, '2026-05-02'),
('11', '相机', 2, 3, '2026-05-03'),
('05', '相机', 3, 1, '2026-05-03'),
('05', '相机', 2, 2, '2026-05-04'),
('10', '相机', 4, 3, '2026-05-03'),
('14', '相机', 2, 2, '2026-05-03'),
('02', '微信', 2, 3, '2026-05-02'),
('04', '微信', 6, 3, '2026-05-01'),
('08', '微信', 1, 1, '2026-05-01'),
('03', '大众点评', 4, 2, '2026-05-03'),
('09', '大众点评', 3, 2, '2026-05-02'),
('12', '大众点评', 6, 5, '2026-05-01');本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2026-06-02，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号