计算用户的平均次日留存率

描述

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

示例：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

解答

select count(date2) / count(date1) as avg_ret
from (
    select distinct a.device_id,
        a.date date1,
        b.date date2
    from question_practice_detail a
    left join(
        select distinct device_id
            ,date
        from question_practice_detail
    ) b
    on a.device_id=b.device_id
    and date_add(a.date, interval 1 day)=b.date
) c

1、不能使用dense_rank窗口函数：能考虑一天多次做题的数据，但是不能考虑跨天做题的数据。

2、left join的时候使用and还是where是有区别的：and保留了left的部分，右边可空，而where保留left和right都非空的部分。

and的子查询：

where的子查询