【题目】：
某电商平台面试题：
下面为用户登录表，分别计算用户的次日、3日留存数及留存率
【解题思路】
此题的核心是怎么计算用户的留存数/留存率？用户留存率是电商行业经常用到的指标，用户的留存数指“第一天登录，以后几天还继续登录的用户数”，"留存率=次日的留存数/当日总的用户数"。因此先要计算出每日的总的用户数、次日的留存数，然后再计算留存率。
一、计算每个用户登录天数间隔
利用表的自联结计算间隔天数，a表作为前面登录的表（计算当日的用户数），b表作为后登录的表(计算后面的留存数)，间隔日期为{“b表的登录日期”-“a表的登录日期”}，通过a、b两个表的“用户ID”关联可以计算在某一日用户在后面几日的留存数。
select a.登录序号,a.用户ID,a.登录日期 as 登录日期a,b.登录日期 as 登录日期b,datediff(b.登录日期,a.登录日期) as 间隔天数from 用户登录表 aleft join 用户登录表 bon a.用户ID=b.用户IDand 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 用户登录表 aleft join 用户登录表 bon a.用户ID=b.用户IDand a.登录日期< b.登录日期order by a.登录序号,a.登录日期) datesgroup by dates.登录日期aorder 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 用户登录表 aleft join 用户登录表 bon a.用户ID=b.用户IDand a.登录日期< b.登录日期order by a.登录序号,a.登录日期) datesgroup by dates.登录日期aorder 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 用户登录表 aleft join 用户登录表 bon a.用户ID=b.用户IDand a.登录日期< b.登录日期order by a.登录序号,a.登录日期) datesgroup by dates.登录日期aorder by dates.登录日期a;
【本题考点】
1、业务的理解。在电商行业、互联网行业经常要计算用户的留存率，怎么用sql语句构建并计算用户的留存数是非常重要的
2、Datediff()函数的应用
Datediff() 函数返回两个日期之间的天数，表达式：
datediff(date1,date2)date1 和 date2 参数是合法的日期或日期/时间表达式，只有值的日期部分参与计算。Datediff计算结果得出的值是Date1-Date2的值。
3.case when 函数的应用
条件筛选时经常用到case when 函数，详细见case when 函数的介绍
【面试题总结】
这道题是实用性比较强的一道题，在互联网行业当中，特别是电商行业，利用拉新或推广的活动把客户吸引到公司的网站、APP，但是经过一段时间可能就会有一部分客户因为不喜欢或者得不到自己想要的而逐渐流失了，那些留下来的人就称为留存，通过用户留存/留存率检核活动的效果，如果用户留存率下降的很快就要查找原因了，毕竟现在获客拉新越来越难，成本原来越高。
⬇️点击「阅读原文」免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
