【Kay】留存率相关题目

一、什么是留存

以12月1日的新增用户为例，如果12月2日也登录了，就算做次日留存；如果12月3日又登录了，就算做三日留存；12月6日再次登录，就算作7日留存

二、SQL实现

计算留存率只需要2个字段：用户ID (id) 和 登录日期 (date)

• table：表名
• id: 用户id
• login：登录日期时间，例如：2020-05-25 16:03:05

Pre:求总的次日留存率

SELECT COUNT(DISTINCT q.device_id,q.date) / COUNT(DISTINCT p.device_id,p.date)
FROM question_practice_detail p
LEFT JOIN question_practice_detail q
ON p.device_id = q.device_id AND DATEDIFF(q.date,p.date) = 1

 

①活跃用户留存率：

指当天活跃用户数除以数天后该批用户在此天仍活跃的用户数；

例：（2019-11-18有100名活跃用户，此100名用户中有60名在 2019-11-25仍活跃，即7天活跃用户留存率为60/100=60%）

以7日留存率为例

SELECT a.login,ROUND(COUNT(b.id)/COUNT(a.id),2) AS '7日留存率'
FROM
(SELECT DISTINCT a.id, DATE(a.login) AS dt
FROM table
ORDER BY dt) a
LEFT JOIN table b 
ON a.id = b.id 
AND a.dt = DATESUB(DATE(b.login), INTERVAL 6 DAY)  #或者DATEDIFF(DATE(b.login), a.dt) = 6

②新用户留存率：

SELECT a.login,ROUND(COUNT(b.id)/COUNT(a.id),2) AS '新用户7日留存率'
FROM
(SELECT DISTINCT a.id, MIN(DATE(a.login)) AS dt
FROM table
GROUP BY a.id
ORDER BY dt) a
LEFT JOIN table b 
ON a.id = b.id 
AND a.dt = DATESUB(DATE(b.login), INTERVAL 6 DAY)  #或者DATEDIFF(DATE(b.login), a.dt) = 6