牛客SQL29详解 计算用户的平均次日留存率

牛客SQL29详解 计算用户的平均次日留存率

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

解题总体逻辑：

1、计算第二天再来的记录数量 (t1):
使用自连接查找在某一日期出现的设备 ID，且该设备 ID 在前一天也有记录。
计算这些设备 ID 和日期组合的唯一数量。

2、计算总记录数量 (t2):
计算 question_practice_detail 表中所有唯一的设备 ID 和日期组合数量。

3、最终计算:
将 t1 的结果（第二天再来的设备数量）除以 t2 的结果（总的设备和日期组合数量），得到平均值 avg_ret。

使用场景：
分析用户的回访情况，即有多少设备在某一天后又回来了。
计算这些设备的比例来评估用户的留存情况。

题解：

select
  (t1.num / t2.num) as avg_ret
from
  ( -- 计算第二天再来的记录数量
    select
      count(distinct a.device_id,a.date) as num
    from
      question_practice_detail a
      left join question_practice_detail b on a.device_id = b.device_id
      and a.date = date_add(b.date, interval -1 day)
    where
      b.device_id is not null
  ) t1,
  ( -- 计算总记录数量
    select
      count(distinct device_id, date) as num
    from
      question_practice_detail
  ) t2;

COUNT() 函数是一个聚合函数，用于计算表中行的数量或特定列的非空值数量。

COUNT(*) 计算表中的所有行，包括 NULL 值
COUNT(column_name) 只计算该列中的 非NULL 值
COUNT(DISTINCT column_name) 计算该列中 唯一且 非NULL 的值，即该列的不同值的数目

count(distinct a.device_id,a.date) as num
计算在 question_practice_detail 表中不同设备（device_id）和日期（date）组合的数量。
这里的 num 是计算的结果，即第二天再来的记录数量。

left join question_practice_detail b on a.device_id = b.device_id
进行自连接，将表 a 与表 b 连接。
条件是 a.device_id 等于 b.device_id，并且 a.date 等于 b.date 的前一天。

也就是说，它在查找那些在某一天（a.date）再次出现的设备 ID（device_id）。

where
      b.device_id is not null
行过滤条件：
确保只有那些在表 b 中有匹配记录的 a 记录被计算。
这意味着设备在前一天确实存在过。

Ending！
更多课程知识学习记录随后再来吧！

就酱，嘎啦！

注：
人生在勤，不索何获。