Hi，大家好，我是编程小6，很荣幸遇见你，我把这些年在开发过程中遇到的问题或想法写出来，今天说一说
怎么进行用户留存分析工作_仓库数据分析怎么做,希望能够帮助你!!!。

【题目】：

某电商平台面试题：

下面为用户登录表，分别计算用户的次日、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 函数的介绍

今天的分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。