牛客网刷题学习SQL（九）

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

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

示例：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 distinct device_id,date from question_practice_detail

在上面的基础上在查出某一天之后用户第二天在答题的情况，例如：通过左连接查询qpd中23号的数据、unique_id中24号的数据，从而筛选出的数据放在date2字段

select distinct qpd.device_id,
    qpd.date date1,
    unique_id.date date2
    from question_practice_detail qpd
    left join (
        select distinct device_id,
        date
        from question_practice_detail
   ) unique_id
   on qpd.device_id = unique_id.device_id
    and date_add(qpd.date,interval 1 day)=unique_id.date

查出来：后面date2没有值的是第二天没有答题
id date1 date2
2138 2021-05-03
3214 2021-05-09
3214 2021-06-15
6543 2021-08-13
2315 2021-08-13 2021-08-14
2315 2021-08-14 2021-08-15
2315 2021-08-15
3214 2021-08-15 2021-08-16
3214 2021-08-16
3214 2021-08-18

所以最后整理出完整的sql：注意count函数并不会统计null

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

date_add函数介绍：

在SQL中，DATE_ADD函数用于对日期进行加减操作。DATE_ADD函数的基本语法如下：
DATE_ADD(date, INTERVAL value unit)
其中，date表示要进行加减操作的日期，value表示要加减的数量，unit表示要加减的单位，可以是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等。
例如，要将日期加上10天，可以使用以下语句：
SELECT DATE_ADD(‘2022-01-01’, INTERVAL 10 DAY);
在上述语句中，'2022-01-01’表示要进行加减操作的日期，INTERVAL 10 DAY表示要加上10天。
需要注意的是，DATE_ADD函数可以对日期进行加减操作，但不能对时间进行加减操作。如果要对时间进行加减操作，可以使用TIME_ADD函数。同时，如果要对日期进行减法操作，可以使用DATE_SUB函数。