字节跳动大数据面试SQL-新增用户数及其留存率

数据仓库晨曦

发布于 2026-06-23 17:32:37

240

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题是第9题"次日留存率"的进阶版。字节跳动面试官经常在候选人写完次日留存后追问："如果我要看3日、7日留存呢？你写9个 UNION ALL？"——这其实是考察"能不能用一次扫描算出多日留存"。

业务场景：产品上线了新功能，需要看"这个功能吸引来的新用户，在第1/3/7/30天的留存率分别是多少"，以此评估功能的长期价值。

二、题目

用户登录日志表 t12_zj_user_login 同第9题。请计算每天的新增用户数，以及他们的次日、3日、7日留存率。

t12_zj_user_login 表

+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| 1        | 2025-01-01  |
| 2        | 2025-01-01  |
| 3        | 2025-01-01  |
| 1        | 2025-01-02  |
| 2        | 2025-01-03  |
| 1        | 2025-01-04  |
| 4        | 2025-01-03  |
| 4        | 2025-01-04  |
| 4        | 2025-01-05  |
| 5        | 2025-01-03  |
| 3        | 2025-01-05  |
| 5        | 2025-01-07  |
+----------+-------------+

期望输出：每天的新增用户数、次日留存率、3日留存率、7日留存率。

三、思路分析

核心与第9题一样，但需要在 LEFT JOIN 时一次关联多个偏移天数：

找每个用户首次登录日 MIN(login_date)
使用case when 计算第1、第3、7日留存用户数
同时 COUNT 多个偏移的 DISTINCT user_id

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：找首次登录

SELECT user_id, MIN(login_date) AS first_date
FROM t12_zj_user_login
GROUP BY user_id;

结果：

+----------+-------------+
| user_id  | first_date  |
+----------+-------------+
| 1        | 2025-01-01  |
| 2        | 2025-01-01  |
| 3        | 2025-01-01  |
| 4        | 2025-01-03  |
| 5        | 2025-01-04  |
+----------+-------------+
5 rows selected (10.96 seconds)(https://www.dwsql.com/)

步骤2：一次 LEFT JOIN 通过计算时间偏移来关联多个偏移天数

with first_login as (
    select user_id, min(login_date) as first_date
    from t12_zj_user_login
    group by user_id
)
select
    f.first_date,
    count(distinct f.user_id) as new_users,
    count(distinct case when date_diff(d1.login_date,f.first_date) = 1 then d1.user_id end) as day1_retained,
    count(distinct case when date_diff(d1.login_date,f.first_date) = 2 then d1.user_id end) as day3_retained,
    count(distinct case when date_diff(d1.login_date,f.first_date) = 6 then d1.user_id end) as day7_retained,
    round(count(distinct case when date_diff(d1.login_date,f.first_date) = 1 then d1.user_id end) / count(distinct f.user_id), 2) as day1_pct,
    round(count(distinct case when date_diff(d1.login_date,f.first_date) = 2 then d1.user_id end) / count(distinct f.user_id), 2) as day3_pct,
    round(count(distinct case when date_diff(d1.login_date,f.first_date) = 6 then d1.user_id end) / count(distinct f.user_id), 2) as day7_pct
from first_login f
left join t12_zj_user_login d1
    on f.user_id = d1.user_id
where d1.login_date > f.first_date
group by f.first_date
order by f.first_date;

最终结果：

+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
| first_date  | new_users  | day1_retained  | day3_retained  | day7_retained  | day1_pct  | day3_pct  | day7_pct  |
+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
| 2025-01-01  | 3          | 1              | 1              | 1              | 0.33      | 0.33      | 0.33      |
| 2025-01-03  | 1          | 1              | 1              | 0              | 1.0       | 1.0       | 0.0       |
| 2025-01-04  | 1          | 0              | 0              | 0              | 0.0       | 0.0       | 0.0       |
+-------------+------------+----------------+----------------+----------------+-----------+-----------+-----------+
3 rows selected (1.023 seconds)(https://www.dwsql.com)

分析：1月1日新增3人，次日仅1人回来(33%)，但第3天有2人回来了(67%)——说明部分用户不是每天都打开，但3天内会再回来。

五、常见坑点

坑1：多 JOIN 性能问题

3 个 LEFT JOIN 各扫一次全表，对于亿级数据会非常慢。这里用 case when 计算每个偏移天数的留存用户数，避免了多次扫描全表。where条件限制d1.login_date > f.first_date，只关联后续登录记录。

坑2：最近7天无法计算7日留存

如1月5日的新增用户，7天后是1月12日——如果数据只到1月8日，7日留存率是 0。实际业务中通常只展示"距今至少N天前"的新增用户。

坑3：N日留存与首次登录日时间间隔

3日留存是在用户首次登录日后的第3天登录，而不是在+3天登录。7日留存是在用户首次登录日后的第7天登录，而不是在+7天登录。

坑4：区分N日留存与N日内留存 有些题目让计算N日内留存，而不是N日留存。

六、举一反三

透视表形式：用 SUM(CASE WHEN DATEDIFF = 1 THEN 1 END) 替代多次 LEFT JOIN，一次 GROUP BY 透视所有天数
留存曲线 SQL：用一个日期维度表 CROSS JOIN 偏移天数(1-30)，左关联登录表，一条 SQL 画出完整留存曲线

七、知识点总结

考点	说明
多表 LEFT JOIN	一次关联多个偏移天数，代码简洁
多个 COUNT DISTINCT	不同别名的 user_id 互不干扰
性能考量	大数据量优先用透视表方案替代多次 JOIN
数据时效	最近N天的新增用户无法计算N日留存

八、建表语句和数据插入

CREATE TABLE IF NOT EXISTS t12_zj_user_login (
    user_id INT,
    login_date STRING
);

INSERT INTO t12_zj_user_login VALUES
(1, '2025-01-01'),
(2, '2025-01-01'),
(3, '2025-01-01'),
(1, '2025-01-02'),
(2, '2025-01-03'),
(1, '2025-01-04'),
(4, '2025-01-03'),
(4, '2025-01-04'),
(4, '2025-01-05'),
(5, '2025-01-04'),
(3, '2025-01-07'),
(5, '2025-01-07');

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-06-14，如有侵权请联系 cloudcommunity@tencent.com 删除