题目：查看用户在某天刷题后第二天还会再来刷题的留存率

首先，分解题目

（一）查找同一个用户第二天的刷题记录，并保留未找到的数据

分析：把日期全部看成第一天，再自连查询第二天（日期间隔为1）的刷题记录。

细节：

  1. 因为一个用户一天内会多次提交所以需要使用distinct关键字去重。
  2. 因为内连接会自动去除未找到的数据，所以需要在join前面加上left

select 
	 distinct a.device_id,a.date day_one,b.date day_two 
from 
	question_practice_detail a 
left join 
	question_practice_detail b 
on 
	a.device_id=b.device_id and datediff(b.date,a.date)=1;#条件：同一个用户，日期间隔为1

+-----------+------------+------------+
| device_id | day_one    | day_two    |
+-----------+------------+------------+
|      2138 | 2021-05-03 | NULL       |
|      3214 | 2021-05-09 | NULL       |
|      3214 | 2021-06-15 | NULL       |
|      6543 | 2021-08-13 | NULL       |
|      2315 | 2021-08-13 | 2021-08-14 |
|      2315 | 2021-08-14 | 2021-08-15 |
|      2315 | 2021-08-15 | NULL       |
|      3214 | 2021-08-15 | 2021-08-16 |
|      3214 | 2021-08-16 | NULL       |
|      3214 | 2021-08-18 | NULL       |
+-----------+------------+------------+
10 rows in set (0.00 sec)

（二）求留存率

由“查询结果1”得知，一共10条记录其中有3条能找到第二天的做题记录。

留存率=two_day总数/总条数=3/10=0.3（符合用例）

这时候就有同学说，老师我懂了，我只要把上面这个查询当作子查询放在from后面然后select后面写count(day_two)/count(*)就可以了。

select 
	count(t.day_two)/count(*) avg_ret 
from 
  (select distinct a.device_id,a.date day_one,b.date day_two 
	from question_practice_detail a 
	left join question_practice_detail b 
	on a.device_id=b.device_id and datediff(b.date,a.date)=1 
  ) t;

对没错你确实可以这么写，但如果我不想用子查询呢?

如果我们把1.1 select后面的语句换成 distinct a.device_id,b.date day_two，就能得到

+-----------+------------+
| device_id | day_two    |
+-----------+------------+
|      2138 | NULL       |
|      3214 | NULL       |
|      6543 | NULL       |
|      2315 | 2021-08-14 |
|      2315 | 2021-08-15 |
|      2315 | NULL       |
|      3214 | 2021-08-16 |
+-----------+------------+
7 rows in set (0.00 sec)

我们只需要关注day_two非空的数据和1.1查出来的是否一致就可以了，不用在意distinct在day_two为null时只根据device_id去重

因为count(distinct a.device_id,b.date)不会计算null值，所以select count(distinct a.device_id,b.date) ...执行结果为3

又因为select count(distinct a.device_id,a.date ,b.date)...执行结果为10

distinct a.device_id,a.date ,b.date可以把b.date简化掉，因为a.date确定a.date也能确定（a.date+1天=b.date）

最终：

select 
count(distinct a.device_id,b.date)/count(distinct a.device_id,a.date) as avg_ret
from
question_practice_detail a
left join
question_practice_detail b
on
a.device_id=b.device_id and datediff(b.date,a.date)=1;

这是我第一次写文章如有错漏或不清晰的地方欢迎指出，谢谢！