【已解决】数据库常见场景应用计算次日留存率

记录一个数据库中常见的应用场景，需要计算用户次日留存率（次留分析），查询出计算的用户留存率。

结合这个应用背景，牛客网站上的 SQL29 计算用户的平均次日留存率 非常适合拿来练习，下面就以牛客上的这道题目作为示例。

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

示例：question_practice_detail

id	device_Id	quest_id	result	date
1	2138	111	wrong	2021-05-23
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

解决方案：

1. 限定条件：首先将表里的数据看作全部都是第一天来刷题的，之后只要构造出第二天来的字段，因此可以考虑使用 left join 把第二天来了的拼接起来，限定第二天来了的可以用 date_add(date1, interval 1 day) 筛选，并用 device_id 限定是同一个用户。

2. 平均概率：可以使用 count(date1) 得到左表全部的 date 记录数作为分母，count(date2) 得到右表关联上了的 date 记录数作为分子，相除即可得到平均概率。

注意事项：

• 表头重命名：as
• 去重：需要按照 devece_id, date 去重，因为一个人同一天可能来多次，但是只需要记录一次用户来过的记录即可
• 子查询必须全部有重命名：尽管可能不会在最终的查询字段中用到，但是必须要写，这是语法规定！

查询代码如下：

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