某电商平台面试题：

下面为用户登录表，分别计算用户的次日、3日留存数及留存率

 

【解题思路】

 

此题的核心是怎么计算用户的留存数/留存率？用户留存率是电商行业经常用到的指标，用户的留存数指“第一天登录，以后几天还继续登录的用户数”，"留存率=次日的留存数/当日总的用户数"。因此先要计算出每日的总的用户数、次日的留存数，然后再计算留存率。

 

一、计算每个用户登录天数间隔

 

利用表的自联结计算间隔天数，a表作为前面登录的表（计算当日的用户数），b表作为后登录的表(计算后面的留存数)，间隔日期为{“b表的登录日期”-“a表的登录日期”}，通过a、b两个表的“用户ID”关联可以计算在某一日用户在后面几日的留存数。

 

select a.登录序号
,a.用户ID
,a.登录日期 as 登录日期a
,b.登录日期 as 登录日期b
,datediff(b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期;

得到一个用户在各个日期与后面几日的间隔天数的笛卡尔积的表

 

二、计算留存数及留存率

 

1.计算留存数

 

把第一步的结果作为临时表dates，用case when聚合函数筛选出符合间隔的天数，“1”代表次日留存，“3”代表3日留存，以此类推，然后把满足条件的用户ID筛选取出来，因满足条件的用户ID有多个，所以需要用distinct去重，然后对满足条件的”用户ID” 进行计数就行了。SQL语句和结果如下：

select dates.登录日期a
,count(distinct dates.用户ID)  as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

 

 

2.计算留存率

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

 

 

3、扩展一下，后续可以计算3日，7日，10日、30日的留存率，现增加3日留存，把时间间隔更改一下即可 ，

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
,concat(round(count(distinct case when dates.间隔天数=3 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 3日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff(b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

 

 

【本题考点】

 

1、业务的理解。在电商行业、互联网行业经常要计算用户的留存率，怎么用sql语句构建并计算用户的留存数是非常重要的

 

2、Datediff()函数的应用

Datediff() 函数返回两个日期之间的天数，表达式：

 

datediff(date1,date2)

 

date1 和 date2 参数是合法的日期或日期/时间表达式，只有值的日期部分参与计算。Datediff计算结果得出的值是Date1-Date2的值。

 

3.case when 函数的应用

条件筛选时经常用到case when 函数，详细见case when 函数的介绍