大厂面试经典高阶SQL题--次日留存率

为什么写这么一篇文章呢，因为在之前的基金公司面试，1/50的录取几率，很荣幸自己可以打败这么多候选人，被上海著名某基金公司录取。有一大半人卡在了笔试环节，就说我自个厂商招的一群小伙伴，在办公室做题的时候，很多小伙伴直接放弃了，知道自己连笔试都过不了。

而次日留存率，就是其中的第一道笔试题，足以说明它要求面试者的SQL素养之高。

ok，接下来给大家详解一下本题的思路。

0，题目需求

有一张用户登录表（user_login_table），请统计每个日期新用户的次日留存率。

user_id	client_id	login_date
1	A002	2024-11-12
2	A005	2024-11-12
3	A002	2024-11-12
1	A006	2024-11-13
2	A001	2024-11-13
3	A0009	2024-11-14
4	A0008	2024-11-14
4	A0007	2024-11-15
5	A0249	2024-11-15

在这里，有一个特别重要的概念，需要跟大家解释一下：
次日留存率：在一段时间（如第一天）的新增用户中，在第二天再次成功启动应用登录的比例。

1，解题思路

首先，审题之后，总归是要知道他的一个最终的结果的。

咱把结果数据展示一下：

date	rate_of_next_surv	--计算过程
2024-11-12	0.667	（1,2）/（1,2,3）
2024-11-13	0	/
2024-11-14	0.5	（4）/（3,4）

最终的结果，咱只展示date和rate_of_next_surv这两个字段即可。

date	ratesurv
2024-11-12	0.667
2024-11-13	0
2024-11-14	0.5

这么得来的呢？？

完我们在计算某天留存率的时候，当然是看看下一天，有哪些客户还存在登录（当然，就需要客户去关联），但是，时间也需要错位关联！！！关联上的，就是留存下来的总人数。

2，解题代码

--有一张用户登录表（user_login_table），请统计每个日期新用户的次日留存率。

--user_id	 client_id	login_date  date	ratesurv


select count(t1.user_id)--统计当天的所有用户数

      ,t1.login_date

      ,count(t2.user_id)  --统计后一天关联上的用户，注意count（字段），会忽略字段的null值

      ,count(t2.user_id)/count(t1.user_id) as  ratesurv  --次日留存率

from      user_login_table t1

left join user_login_table t2

on t1.user_id = t2.user_id

and t1.login_date = date_sub(t2.user_id,-1)

group by t1.login_date

;

上面涉及到两个重要知识点：

1，属于表的自关联，关联条件，除了用户，还有时间（错位关联）。

2，count(用户)，会忽略字段为null的数据，言外之意，没有关联上的，就不统计。

当然，笔试过程中我们保留所需要的字段展示就可以了。

题目不难，但思路很重要。

---

欢迎一键三连，下次再会！