校招算法笔面试 | SQL笔试面试编程题-计算用户的平均次日留存率

题目## 题目

题目链接

题目描述

运营团队希望查看用户在某天刷题后第二天是否会继续刷题的留存率。具体来说，计算所有用户在某天进行刷题活动后，第二天还进行刷题的用户比例，并求其平均值。需要从 question_practice_detail 表中提取相关数据，最终返回平均留存率 avg_ret，结果保留四位小数。

解题思路

要计算用户的留存率，我们需要完成以下步骤：

1. 确定每个用户每日的刷题记录：

   • 首先，获取每个用户在每个日期的刷题记录，确保每个用户每天只被计算一次。

2. 判断用户次日是否有刷题记录：

   • 对于每个用户在某一天的刷题记录，检查用户在次日是否有刷题行为。如果有，则认为该用户在次日有留存。

3. 计算留存率：

   • 留存率的计算公式为：
     $
     \text{留存率} = \frac{\text{有次日刷题记录的用户总数}}{\text{总用户刷题记录数}}
     $
   • 最终结果保留四位小数。

SQL 查询

SELECT 
    ROUND(
        SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) , 
        4
    ) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p1
LEFT JOIN 
    (SELECT DISTINCT device_id, date FROM question_practice_detail) p2
ON 
    p1.device_id = p2.device_id 
    AND p2.date = DATE_ADD(p1.date, INTERVAL 1 DAY);

代码解释

• 子查询 p1 和 p2：

  • p1：提取每个用户每天的唯一刷题记录。
  • p2：同样提取每个用户每天的唯一刷题记录，用于与 p1 进行自连接。

• LEFT JOIN 连接条件：

  • 连接条件为同一 device_id 且 p2.date 是 p1.date 的次日（即 p1.date 加一天）。
  • 通过这种方式，可以判断用户在某天刷题后次日是否有刷题记录。

• SUM(CASE WHEN p2.device_id IS NOT NULL THEN 1 ELSE 0 END)：

  • 统计有次日刷题记录的用户数量。如果 p2.device_id 不为空，说明用户在次日有刷题行为，计数加1。

• COUNT(*)：

  • 统计总的刷题记录数，即总的 (device_id, date) 组合数量。

• ROUND(..., 4)：

  • 将留存率结果保留四位小数。

题目链接

题目描述

运营团队希望计算出 2021年8月 每天用户练习题目的数量。需从 question_practice_detail 表中提取相关数据，并按照日期统计每天的题目数量。

解题思路

• 我们需要从 question_practice_detail 表中筛选出 2021年8月 的数据。

• 提取日期中的日（day）部分，并统计每一天的题目数量（question_cnt）。

• 注意事项：

  • 确保日期的范围仅限于 2021年8月，避免包含其他月份的数据。
  • 使用 GROUP BY 进行分组统计时，需要基于提取的 day。

SQL 查询

-- 统计2021年8月每天的用户练习题目数量
SELECT 
    DAY(date) AS day,                  -- 提取日期中的日部分
    COUNT(*) AS question_cnt            -- 统计每个日的题目数量
FROM 
    question_practice_detail
WHERE 
    date >= '2021-08-01' AND date < '2021-09-01'  -- 过滤出2021年8月的数据
GROUP BY 
    day                                -- 按日进行分组