SQL 计算次日留存率

Table of Contents

SQL 计算次日留存率 #

计算留存率是 SQL 的典型题目, 这里以牛客网的题目为例. 数据表如下:

{
  "question_practice_detail": [
    {
      "id": "编号",
      "device_id": "用户使用的设备编号",
      "quest_id": "题目编号",
      "result": "答题结果",
      "date": "日期"
    }
  ]
}

要求返回的结果为:

{
  "result": [
    {
      "avg_ret": "平均留存率"
    }
  ]
}

Solution 1 #

为了找出所有在 “第二天” 刷了题的用户, 我们可以将 question_practice_detail 表自连接, 连接规则为 device_id 相同且 date 相差一天. 为了统计方便, 我们使用 LEFT JOIN, 这样表中包含了有第二天记录的用户, 也包含了没有第二天记录的用户. 其中 date1 为第一天的日期, date2 为第二天的日期. 为了避免重复计算, 我们使用 DISTINCT 去重. 最后, 我们只需要计算 date2 的个数除以 date1 的个数即可.

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;

Solution 2 #

使用 LEAD 函数结合 PARTITION BY 找出每一个用户第二次刷题的数据记为 date2, 然后使用 DATEDIFF 函数计算 date2 与 date1 的差值, 如果差值为 1, 则记为 1, 否则记为 0. 最后求平均值即可.

SELECT AVG(IF(DATEDIFF(date2, date1) = 1, 1, 0)) AS avg_ret
FROM (
    SELECT
        DISTINCT device_id,
        date AS date1,
        LEAD(date) OVER (PARTITION BY device_id ORDER BY date) AS date2
    FROM (
        SELECT DISTINCT device_id, date
        FROM question_practice_detail
    ) AS uniq_id_date
) AS id_last_next_date;