（四）SQL面试题（连续登录、近N日留存）学习简要笔记 #CDA学习打卡

目录

一. 连续登录N天的用户数量

1）举例题目

2）分析思路

3）解题步骤

（a）Step1：选择12月的记录，并根据用户ID和登录日期先去重

（b）Step2：创建辅助列a_rk（每个userID下的日期排序值）

（c）Step3：创建辅助列起步时间b_createdTime（用登录日期减去排序值，得到新时间列

（d）Step4：根据起步时间列统计连续登录天数

（e）Step5：根据统计结果查询连续登录人数（题目要求连续7天）

二. 近N日留存的用户数及留存率

1）举例题目

2）分析思路

3）解题步骤

（a）Step1：根据用户id和登录日期先去重

（b）Step2：创建新列first_time，获取每个userID下的最早登录日期

（c）Step3：创建辅助列delta_time，用登录日期列减去最早登录日期first_time，得到留存天数

（d）Step4：按first_time列统计不同留存天数对应的次数和即某日的近N日留存数

（e）Step5：用某日的近N日留存数除以首日登录人数即留存率

---

一. 连续登录N天的用户数量

1）举例题目

现有用户登录表(user_active_Iog)一份，里面有2个字段：userlD(用户ID)，createdTime(登录时间
戳)，需要统计2021年12月连续登录7天的用户数量。

2）分析思路

题目要求的核心是连续登录，那么我们思考，何为连续登录呢？ 顾名思义，连续登录就是指登录的日期连续，那么用数据库的语言来表达的话，我们该描述表达日期连续呢？ 我们简化一下数据来考虑这个问题，一般我们有2个办法：

方法A：构造一个连续数字构成的辅助列，用原始日期减去辅助列的数字，得到一个新日期，根据这个新日期来判断是否连续。

方法2：构造一个连续日期构成的辅助列，用原始日期减去这个辅助列的日期，得到一个新数字，最后根据这个数字来判断连续。 

一般我们为了方便统计某一起始时间连续登录了多少天，多半采用方案A。

3）解题步骤

我们接着看，刚才知道了如何在SQL里面如何描述连续登录，接下来我们逐步按照题目要求拆解即可：

（a）Step1：选择12月的记录，并根据用户ID和登录日期先去重

（注：单个用户一天有多行登录数据的情况，只保留1行)

运行结果如下：

（b）Step2：创建辅助列a_rk（每个userID下的日期排序值）

运行结果如下：

（c）Step3：创建辅助列起步时间b_createdTime（用登录日期减去排序值，得到新时间列

运行结果如下：

（d）Step4：根据起步时间列统计连续登录天数

运行结果如下：

（e）Step5：根据统计结果查询连续登录人数（题目要求连续7天）

运行结果如下：

二. 近N日留存的用户数及留存率

1）举例题目

现有用户登录表(user_active log)一份，里面有2个字段：userID(用户ID)，createdTime(登录时间 戳)，需要统计近1、2、3、5、7、30日留存用户数量及留存率。

2）分析思路

题目要求的核心是近N日留存，那么我们思考，何为近N日留存呢？顾名思义，就是指距离某个日期的间隔为N，那么用数据库的语言来表达的话，我们该描述表达近N日留存呢？

我们简化一下数据来考虑这个问题：构造一个起始日期构成的辅助列，用原始日期减去辅助列的日期，得到一个新数字N，根据这个新数字，结合起始日期来判断某个日期的近N日留存。

3）解题步骤

（a）Step1：根据用户id和登录日期先去重

运行结果如下：

（b）Step2：创建新列first_time，获取每个userID下的最早登录日期

运行结果如下：

（c）Step3：创建辅助列delta_time，用登录日期列减去最早登录日期first_time，得到留存天数

运行结果如下：

（d）Step4：按first_time列统计不同留存天数对应的次数和即某日的近N日留存数

运行结果如下：

（e）Step5：用某日的近N日留存数除以首日登录人数即留存率

运行结果如下：

Bye！