首页 > 试题广场 >

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

[编程题]计算用户的平均次日留存率

热度指数：330381 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

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

示例：question_practice_detail

id device_id question_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

8 3214 112 wrong 2021-05-09

9 3214 113 wrong 2021-08-15

10 6543 111 right 2021-08-13

11 2315 115 right 2021-08-13

12 2315 116 right 2021-08-14

13 2315 117 wrong 2021-08-15

14 3214 112 wrong 2021-08-16

15 3214 113 wrong 2021-08-18

16 6543 111 right 2021-08-13

根据示例，你的查询应返回以下结果：

avg_ret

0.3000

示例1

输入

drop table if  exists `question_practice_detail`;
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);

INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');

输出

avg_ret
0.3000

算法知识视频讲解

Mysql

fuyuAme233

我是废物

发表于 2021-09-02 08:36:22 回复(191)

Mysql

Taku什么时候上岸

翻遍了讨论区和解题去的答案，觉得这个解法是最好了。

SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 left outer join question_practice_detail as q2
on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1

思路是：

1. 需要知道两天都上线的人数

2. 需要知道第一天上线的人数

做法：

1. 用datediff区分第一天和第二天在线的device_id

2. 用left outer join做自表联结

3. 用distinct q2.device_id,q2.date做双重去重，找到符合条件的当天在线人数

发表于 2021-09-30 21:41:36 回复(83)

恶作剧之神阿蒙

想不出来，参考的其实是牛哥大佬的答案

做一下笔记

select avg(if(b.device_id is not null,1,0)) as avg_ret
from 
(select distinct device_id,date
from question_practice_detail
)a
left join 
(
select distinct device_id,date_sub(date,interval 1 day) as date 
from question_practice_detail
)b
on a.device_id = b.device_id and a.date = b.date

DATE_SUB() 函数从日期减去指定的时间间隔。
代码中就是减去一天

原本日期

date_sub(date,interval 1 day)

left join 后

思路就是找到隔天日期后还会来的同一个人，取平均值3/10

发表于 2021-08-25 11:37:54 回复(56)

Mysql

鹅鸭杀没鸡

select count(t2.date)/count(t1.date) from
    (select distinct device_id,date from question_practice_detail) t1
    left join 
    (select distinct device_id,date from question_practice_detail) t2
    on t1.device_id = t2.device_id 
    and t2.date = date_add(t1.date,interval 1 day)

发表于 2021-09-28 23:47:09 回复(17)

GarvinMu

借鉴的到目前为止是2楼的答案，但加上自己的一些理解：(为简便，id=deviceid)

1. 根据需求首先应该想到计算公式是什么，应该是

(滞后一天日期且前一天上线的唯一id的总数量) / (前一天上线的唯一id的总数量)，

也就是用同id但是不同日期的数量进行相除

那么同时考虑id和日期的数量应该考虑同一个id在不同日期的组合

即count(distinct id, date)

2. 那么第二个问题来了，数量有了，但是如何区分日期呢

那也就用到了datediff，则datediff(date1, date2) = 1即可

3. 我们现在有了计算公式的基本元素，那么接下来应该如何执行计算呢

则需要连接两个表，为的就是能够将滞后一天日期的唯一id数量与前一天的进行错位

即select count(distinct q2.id, q2.date) / count(distinct q1.id, q1.date) as avg_reg

from 就这个表 as q1

left outer join 同样这个表 as q2

on q1.id = q2.id

and datediff(date1, date2) = 1;

这里再说一下为什么要用left outer join，因为我们要保证分母的完整性，如果只用了join则会根据联结条件去除q1表中不符合条件的行，则最后结果应该会变成1（自己想的，不知道对不对），所以要用外联结。

发表于 2021-10-15 15:27:21 回复(16)

Mysql

深夜魔法师

一、单独且完整的观察表中数据可以看出4个用户在8天的时间里，产生了10种不同的答题模式（指不同时间且不同用户的情况），其中用户device_id：2315（以下简称2315）在13、14、15号连续三天答题，那么用户2315就有两次的答题模式属于留存，再看用户device_id3214，其在15、16号两天答题，那么3214的留存模式就只有一次，其他用户没有留存模式。则在总共10总模式中，产生了2+1=3次留存模式，那么留存率就为3/10=0.3 这就是题目的意思吗？

二、平均概率的理解

①

如果这样是正确的话，那这个 “用户第二天还会再来的平均概率” 我觉得是个错误表述，否则应该计算每个用户的留存率，然后平均下来。比如说用户2315产生了两次留存，那么他的概率就是2/3（来了三天，其中有两天，这两天中每一天的前一天也是有来过的），同理，用户3214就为1/2. 其他用户为0 平均下来就是（2/3+1/2）/2=7/12.

②或者理解为用户2315在总共10次模式中，产生了2次，那么概率就为0.2 同理下用户3214的概率就为0.1 那么平均概率就为（0.2+0.1）/2=0.15

以上仅为个人不成熟的理解方式，欢迎各位大佬指正哈哈！

发表于 2021-09-27 16:16:40 回复(11)

Mysql

花卷要offer

select count(distinct b.device_id,b.date)/count(distinct a.device_id,a.date) as avg_ret
from question_practice_detail a left JOIN question_practice_detail b
on a.date = b.date-1 and a.device_id = b.device_id

先用左连接，可以得到第一天回答但是第二天没有回答的记录，然后通过count，并且通过和device_id和日期进行去重

发表于 2021-10-30 12:35:11 回复(8)

Mysql

xxno1

窗口函数的解法

select 
    count(id) / (select count(distinct device_id,date) from  question_practice_detail) as avg_ret
from
    (SELECT
        a.device_id as id
        ,a.date as date1 
        ,lead(a.date,1) over(partition by device_id order by a.date) as date2
    from 
        (select distinct device_id,date from question_practice_detail) a
    ) b
where DATEDIFF(date2,date1) =1

发表于 2021-08-26 12:03:56 回复(10)

Mysql

Flora.201903132327831

思路：表的自联结

SELECT COUNT(b.device_id)/ COUNT(a.device_id)
FROM(
    SELECT DISTINCT device_id,date
    FROM question_practice_detail ) as a
LEFT JOIN (
    SELECT DISTINCT device_id,DATE_SUB(date,interval 1 day) as date
    FROM question_practice_detail ) as b
ON a.date=b.date AND a.device_id=b.device_id

发表于 2021-09-30 15:04:58 回复(5)

Mysql

Reg333

SELECT 
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as q1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q2.date = DATE_ADD(q1.date, interval 1 day)

发表于 2021-10-01 15:34:57 回复(7)

那位大人

想不出怎么得到留存率，公式就把我难住了😣

发表于 2021-09-23 15:49:49 回复(3)

鵺落无声

select count(date2)/count(date) as avg_ret
from

#第一天用户

(select distinct device_id,date
from question_practice_detail) a
left join

#第二天用户,用date_sub把第二天的日期改为第一天的日期，从而把第二天的数据匹配到第一天上

(select distinct device_id,date_sub(date,interval 1 day) date2
from question_practice_detail) b
on a.device_id=b.device_id and a.date=b.date2;

#只有在满足两个限定条件的情况下，date2才能匹配出数值，不能匹配的情况下，即第二天没有刷题，date2 is null
#计算用户的平均次日留存率

发表于 2021-09-17 11:00:02 回复(3)

绿茵程序员

这是非技术的SQL么🤓

发表于 2021-10-28 01:30:49 回复(3)

Mysql

Finalize

这道题确实有些难，吐槽一下真的要实现这个功能我宁愿写两个SQL查出来再进行计算，性能差也比花半天时间来写强

思路

首先要搞明白这个概率的计算，不然无从下手
概率=(去重后的用户有连续两天刷题记录次数)/(去重日期用户后刷题记录次数)
最好先写成两句SQL，分别查出分母和分子的记录数，然后用子查询结合起来就好了
SQL1
1.1 先来找有连续两天刷题的记录，显然单靠本表查询是做不到的，这里应该用到自连接
1.2 自关联后要进行根据日期和device_id进行进一步筛选，然后求count得到分子
(这里建议将案例sql复制到MySQL数据库一点点查着写，就清楚了，语言描述还是苍白无力)
```
select count(distinct q3.date,q3.device_id) from (select
q1.device_id,q1.date
from
question_practice_detail q1 left join question_practice_detail q2
on q1.device_id = q2.device_id
where date_add(q1.date,interval 1 day)=q2.date) q3;
```
sql2
2.1 对原表进行查询，采用相同的手段进行去重,最后再求count得到分母
```
select count(DISTINCT date,device_id)
from question_practice_detail;
```
子查询
这题之所以用子查询是因为非要一个SQL计算出结果的无奈之举。目的是为了把上面两句SQL结果在一句SQL用起来进行计算。我是将SQL1作为子SQL的。
ps：子查询最好多结合别名来使用
```
select q5.times/count(DISTINCT q4.date,q4.device_id) avg_ret
from question_practice_detail q4, (select count(distinct q3.date,q3.device_id) times from (select 
q1.device_id,q1.date
from 
question_practice_detail q1 left join question_practice_detail q2
on q1.device_id = q2.device_id
where date_add(q1.date,interval 1 day)=q2.date) q3) q5;
```

发表于 2021-10-01 20:52:05 回复(5)

Mysql

冷凡社长

select round(count(distinct t1.device_id,t1.date)/count(distinct t.device_id,t.date),4) as avg_ret
from question_practice_detail t
left join 
question_practice_detail t1
on t.device_id = t1.device_id and datediff(t1.date,t.date)=1

这个题看题目是次日留存的，其实本质是考察表的自联结的。
但易错的点不在这里。
作为老手，第一时间想到的就是自联结。
然后思路就是使用第二天来的人/除以第一天来的人即可
然后写了
count(distint t1.device_id)/count(distint t.device_id)
这么写是因为没有第二天的数据，不会左联到T表，就为null,count(列)是统计非null的行数。但是这个结果不对。
然后把数据导入到本地调试一下，然后明白了代表一个用户一天的情况，需要把天也加进来，device_id+date才能标识唯一。
然后就求出正确的答案了。
count本身不能求多列，但是加入了distinct 就把多列变为一个组了，就可以了。

本题重点

1、使用自联结：表自己与自己链接
2、明白count用法
- count(列) 统计非null行数
- count(多列不行) count(distinct 多列可以)
3、datediff的用法：前面的日期 - 后面的日期。单位是天。【mysql】用法

发表于 2022-07-15 15:58:55 回复(3)

牛客959320403号

参考了答案之后，按自己理解的写的，有点长，但是好理解：

1.题目没有展示完整的表，自己用select * from 原表，就会发现有的用户，同一天做了好几次题目，所以要把device_id，date一起去重，剔除同一用户同一天做题的重复项；2.去重之后，用datediff函数，同样的表查交集，得出第一天做题之后第二天也做题的表，用count（*）得出符合题目条件的行数；3.用步骤2的行数除以步骤1去重后的行数，就是题目所求。

select (select count(*)

from (select distinct device_id,date from question_practice_detail) t1
inner join (select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id
where datediff(t2.date,t1.date)=1)/(select count(*) from (select distinct device_id,date from question_practice_detail) t3) avg_ret

发表于 2022-05-26 21:05:51 回复(1)

Mysql

江上一小白

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

数据对象：一个用户一天做题为一条记录，“第二天还会再来”表示同一个用户在第二天也做了题目，
数据案例：用户A-2022-03-18，用户A-2022-03-19，出现了第二天还会再来
难点：
① 注意去重，同一个用户同一天多次做题，这个只作为一条记录，需要同时对（device_id,date）进行去重（存在一个有些不确定的地方：同一个人多次“第二天再来”是否只算一次，是以人为计量，还是以人+日期进行计量？题目是以人+日期计量）
② “第二天”这个概念计算，可以通过MySql中的日期函数data_add(date, interval 'num' 'type'),num表示数字，type表示日期类型，如day、month、year等
③确定再来的记录数量，通过表关联（left join ... on ...），左表为第一天做题，右表为第二天做题，可以将第二天做题时间-1，此时当设备id，做题日期相同时，如果两表都有数据，则符合“第二天还会再来”,如果两表有一个不存在，则不符合条件

在开始的时候，我是以人为计量进行计算，所以先进行了关联，最终去重是device_id，计算错误；又想到了①，对（device_id,date）。
初始sql如下：

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;

后期可以根据sql 的执行计划继续再次优化

发表于 2022-03-18 22:30:12 回复(7)

Mysql

李玉婷的旁听生

学到了，居然可以这么去重：COUNT(DISTINCT q2.device_id,q2.date)

1、正确写法，将日期&用户对比去重，不仅仅是用户

感谢@Taku什么时候上岸

SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret
from question_practice_detail as q1 
    left outer join question_practice_detail as q2
    on q1.device_id=q2.device_id 
    and DATEDIFF(q2.date,q1.date)=1

2、如图：3/10=0.3

COUNT(DISTINCT q2.device_id,q2.date) -- 3

COUNT(DISTINCT q1.device_id,q1.date) -- 10

3、

不正确：COUNT(distinct qq.device_id)

正确：COUNT(distinct qq.device_id，qq.date)

SELECT COUNT(distinct qq.device_id)/COUNT(distinct q.device_id) AS avg_ret
FROM question_practice_detail q
LEFT JOIN question_practice_detail qq 
ON qq.device_id=q.device_id
AND q.date=qq.date-1

发表于 2021-10-06 21:00:55 回复(4)

我的世界01号

唉，要是这种题目有算出avg_ret公式或者解法就好了。

发表于 2021-12-04 21:40:11 回复(0)

登dua郞

直接裂开，我只想做个小运营，这个以后工作了只能靠自己去取数了吗😭，2年没数学的我完全没思路

发表于 2021-09-26 15:18:48 回复(6)

提交观点

问题信息

SQL

难度：

989条回答 23378收藏 10149浏览

通过挑战的用户

查看代码

独特的奶酪胖乎乎

2023-03-08 22:17:41
松林子木

2023-03-07 11:56:49
躺平了的变色龙很神奇

2023-03-07 11:21:10
牛客18025...

2023-03-06 09:39:55
牛客97559...

2023-03-06 09:27:41

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

输入

输出

思路

SQL1

sql2

子查询

本题重点

问题信息

热门推荐

通过挑战的用户

相关试题

id	device_id	question_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
8	3214	112	wrong	2021-05-09
9	3214	113	wrong	2021-08-15
10	6543	111	right	2021-08-13
11	2315	115	right	2021-08-13
12	2315	116	right	2021-08-14
13	2315	117	wrong	2021-08-15
14	3214	112	wrong	2021-08-16
15	3214	113	wrong	2021-08-18
16	6543	111	right	2021-08-13