计算用户次日留存率

来自牛客网 SQL快速入门29题

描述

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

示例：question_practice_detail

id	device_id	quest_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15
……

根据示例，你的查询应返回以下结果：

avg_ret

0.3000

知识讲解：

首先，了解一个新函数DATE_ADD(日期类型数据，interval n day) 

返回值：日期列+n天

日期列：如2009-09-01

interval n day：2009-09-01 加上 n 天

实现代码：

SELECT 
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

细节讲解：

1.由于是left join，q1是不会变的，q2会根据on的要求变化。

q2.date = DATE_ADD(q1.date, interval 1 day)筛选出q2中符合q1.date+1天的数据，且唯一识别设备名要相同。

所以q2的含义是第二天继续登录的用户的数据。

2.distinct：去除完全相同的数据行，这要求distinct后的所有列都要符合这一要求

形象地说，如果只选一列device_id, 那么很多的2315只会显示一个

但是同时选上了device_id, date，那么只要2315的date不同，就会都显示：

2138    2021-05-03
3214    2021-05-09
3214    2021-06-15
6543    2021-08-13
2315    2021-08-13
2315    2021-08-14
2315    2021-08-15
3214    2021-08-15
3214    2021-08-16
3214    2021-08-18

来自Leetcode 550题：游戏玩法分析4

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列的组合）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

结果格式如下所示：

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

代码：

select round(count(distinct t2.player_id)/count(distinct t1.player_id), 2) as fraction

from (select distinct player_id, min(event_date) first_date from activity

          group by player_id) as t1

          left join (select distinct player_id, event_date from activity) as t2

          on t1.player_id = t2.player_id and t2.event_date = date_add(t1.first_date, interval 1 day);

细节讲解：

1.保留小数：round(数值列，保留n位小数)

2.该题要求 1个player_id计1人，因此分母部分要用distinct 去除重复player_id

3.题目特殊要求，必须首日登录：因此取出每个用户的首日登录数据作为t1，要用min取首日，group by 按每个玩家分组。