【SQL屠夫系列】- 高频面试之SQL计算用户留存率

在如今的程序员面试过程中,考察SQL部分能力,虽不是难点,但几乎是必考.
为检查思路盲点,避免翻船,各位看官也可以复盘下,如对你有一丝的帮助,欢迎给个赞叭~
ps: 数据开发者，建议可以自己加难度进行考察

难度：中等
面试频率：超高 （遇到过类似题型3次+）

指标背景

留存率是用于反映网站、互联网应用或网络游戏的运营情况的核心统计指标，其具体含义为在统计周期（周/月）内，每日活跃用户数在第N日仍启动该App的用户数占比的平均值。其中N通常取2、4、8、15、31，分别对应次日留存率、三日留存率、周留存率、半月留存率和月留存率。

总之留存指标对于toC的企业是非常重要的指标，很多时间我们验证一些策略，比如推荐系统AB测试时，比的就是不同渠道不同算法策略的留存率，以此验证提升的效果。

对于toC的企业，数据亦或是大数据相关的面试我认为，熟练手写常用的用户留存率指标SQL还是非常必要的。

一般企业是以这种留存日历形式进行展示：效果一目了然

指标计算

留存率=每日活跃用户数在第N日仍启动该App的用户数占比的平均值 （一般统计周期为周/月）

比如 1号有100个用户登陆（也可以是注册）了（这100个用户就是1号的活跃用户），到2号的时候又有100个用户登陆了，但这100个用户之中只有20位是昨天登陆的用户，当么2日的留存率就是20/100=20%。

PS: 其实用新增的注册人数更准确点~~

某企业真实留存报表（注渠道与具体用户数已隐藏/脱敏）：

真题

一般可能就直接给你一个登陆login流水表；就两字段

• 用户字段
• 登陆时间字段
  让你求N天的留存率，比如计算7天留存。

简单mock点数据如下：

思路

这种题目首先是要有思路，思路有了代码就出来了

1. 关联登陆时间比自己user_id A大的自己user_id B，取出对应的登陆时间
2. 计算A，B登陆时间的差值，得出后续自己登陆时的天数差值
3. 按1-N日的差值进行count统计

细节：去重与空值
见下方代码

开撸

代码根据思路随意写个，读者可以自行根据需求发挥。

拓展

你还有更好的解法或者思路吗？

---