SQL计算用户留存率问题

概念

电商业务中需要计算用户留存情况，在时间维度上可以分为用户次日、多日后的留存情况，用留存率表示。留存率计算方式如下：

次日留存率 = 当日新用户在次日登录的数量 / 当日新用户注册数量

三日留存率 = 当日新用户在第四天登录的数量 / 当日新用户注册数量

七日留存率 = 当日新用户在第八天登录的数量 / 当日新用户注册数量

其他时间的留存率计算同理。

情况一

假设现在有两张表，一张是注册信息表register_info，另一张是登录信息表login_info，两个表格数据如下。

现在分别计算出每日的新增用户数，以及次日、三日、七日留存率。

首先，将两张表做一个连接，用datediff函数选出用户注册后七日仍有登录的数据（条件1），连接条件是用户id相同（条件2）。查询代码如下：

查询结果如下：（这里每个用户的注册时间只有一个，所以会得出每个用户所有注册登录时间的排列组合便于后续计算）

之后利用case条件判断和count函数计算出各种留存率情况。代码如下：

得到结果如下：

情况二

假如现在只有一张用户登录信息表user_login_info，其中默认每个用户最早的登录时间就是注册时间，表格数据如下：

所以我们需要先用两个子查询得出类似（1）中的注册表t1和登录表t2，之后操作与（1）中相同，代码如下：

其中t3就是情况一中得到的t1.    

查询结果如下：