用户留存率是什么？如何用sql求出次日留存率？

建表sql语句：

drop table if exists liucunlv;
create table liucunlv as select * from ( 
select 'a' as user_name, '2021-03-01'as logindate
 union all 
select 'a' as user_name, '2021-03-02'as logindate
 union all 
select 'a' as user_name, '2021-03-03'as logindate
 union all 
select 'a' as user_name, '2021-03-05'as logindate
 union all 
select 'b' as user_name, '2021-03-02'as logindate
 union all 
select 'b' as user_name, '2021-03-03'as logindate
 union all 
select 'c' as user_name, '2021-03-02'as logindate
 union all 
select 'c' as user_name, '2021-03-04'as logindate
 union all 
select 'c' as user_name, '2021-03-05'as logindate
 union all 
select 'c' as user_name, '2021-03-06'as logindate
 union all 
select 'c' as user_name, '2021-03-07'as logindate
 union all 
select 'd' as user_name, '2021-03-07'as logindate) t

SELECT * FROM liucunlv

一：现在要求出哪些用户才是次日留存的用户

SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name

如果logindate和newdate相差一天，那么就让if_ciriliucun=1否则if_ciriliucun=0

SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucun
FROM 
(
SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name
)t1
JOIN liucunlv t2 ON t1.user_name=t2.user_name

之后再做一个groupby user_name,就能选出是否是次日留存的用户了

SELECT t3.user_name,MAX(if_ciriliucun) FROM
(
SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucun
FROM 
(
SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name
)t1
JOIN liucunlv t2 ON t1.user_name=t2.user_name)t3 GROUP BY t3.user_name

可以看到，a和b是次日留存的用户

二：求每日的次日留存率
留存率解释：
在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。

SELECT t1.logindate AS 日期,
       COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,
       COUNT(DISTINCT t2.user_name) AS 新增用户数,
       CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name
GROUP BY t1.logindate

求每日的次日留存率的整体代码如果读起来有困难，可以尝试接着往下读：
将求每日的次日留存率的整体代码拆解如下：：
t1:

SELECT DISTINCT logindate
   FROM liucunlv

t2:

SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name

t3:

将t1,t2,t3合起来得到一个表a

SELECT *
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name

再将表a group by t1.logindate (表a的第一列)，做一些聚合运算，便得到

SELECT t1.logindate AS 日期,
       COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,
       COUNT(DISTINCT t2.user_name) AS 新增用户数,
       CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name
GROUP BY t1.logindate

得到我们想要的次日留存率：

引用：
https://blog.youkuaiyun.com/weixin_38617657/article/details/116697972