n日留存率问题

最新推荐文章于 2024-05-15 20:16:36 发布

pyq@2001

最新推荐文章于 2024-05-15 20:16:36 发布

阅读量243

点赞数 3

分类专栏： SQL专栏文章标签：面试 sql 大数据数据库

本文链接：https://blog.csdn.net/weixin_55015548/article/details/134090493

版权

SQL专栏专栏收录该内容

6 篇文章 0 订阅

订阅专栏

求n日留存率问题是SQL面试题中的一道经典题目。

1. 基础知识

某日活跃用户（用户 id）在后续的一周内的留存情况（计算次日留存用户数，3 日留存用户数，7 日留存用户数）
指标定义：
- 某日活跃用户数，某日活跃的去重用户数。
- N 日活跃用户数，某日活跃的用户数在之后的第 N 日活跃用户数。
- N 日活跃留存率，N 日留存用户数 / 某日活跃用户数
- 例：登陆时间（2023-04-01）去重用户数 10000，这批用户在 (2023-04-07) 日仍有 7000 人活跃，则 7 日活跃留存率为 7000/10000=70%
- 次 N 日活跃用户数，某日活跃的用户数在之后的第 N + 1 日活跃用户数。
- 次 N 日活跃留存率，N + 1 日留存用户数 / 某日活跃用户数
- 例：登陆时间（2023-04-01）去重用户数 10000，这批用户在 (2023-04-08) 日仍有 7000 人活跃，则次 7 日活跃留存率为 7000/10000=70%

2. 通用解题思路：

求解用户访问量uv，首先就要对数据做一个去重操作，保证同一用户一天只有一条记录。
做自连接操作，通过用户id以及右表日期与左表日期作差值大于等于0两个条件进行关联。关联之后计算出右表日期与左表日期的差值字段。此时我们会发现，差值为0就代表该用户当天的记录，差值为1就代表该用户次日的记录，差值为7就代表该用户次七日的记录。
对左表日期字段进行聚合操作，再通过if判断进行计数就可以得出相应的uv指标。

3. 面试真题一：

3.1 准备工作

drop database if exists db_1;

create database db_1;

use db_1;


create table if not exists db_1.tb_baidu
(
    cuid            varchar(32) comment '用户的唯一标识',
    os              varchar(32) comment '平台',
    soft_version    varchar(32) comment '版本',
    event_day       varchar(32) comment '日期',
    visit_time      int         comment '用户访问时间戳',
    duration        int         comment '用户访问时长',
    ext             varchar(32) comment '扩展字段'
);

insert into db_1.tb_baidu
values
 (1, 'android', 1, '2020-04-01', 1234567, 100, ''),
 (1, 'android', 1, '2020-04-02', 1234567, 100, ''),
 (1, 'android', 1, '2020-04-08', 1234567, 100, ''),
 (2, 'android', 1, '2020-04-01', 1234567, 100, ''),
 (2, 'android', 1, '2020-04-02', 1234567, 100, ''),
 (2, 'android', 1, '2020-04-08', 1234567, 100, ''),
 (3, 'android', 1, '2020-04-01', 1234567, 100, ''),
 (3, 'android', 1, '2020-04-08', 1234567, 100, ''),
 (3, 'android', 1, '2020-04-09', 1234567, 100, ''),
 (5, 'android', 1, '2020-04-02', 1234567, 100, '')
;

select * from tb_baidu;

3.2 目标

说明: 写出用户表 20200401 的次日、次7日留存的具体SQL
- 一条sql统计出以下指标（留存率保留两位小数）
- （4.1号uv，4.1号的次日uv，4.1号的次七日uv，4.1号的次日留存率，4.1号的次七日留存率）
输入

输出

3.3 求解思路

按照通用解题思路进行解题

1、数据去重后

2、自连接求出差值字段

3、聚合计数统计uv等指标（题目要求只求解4.1日的指标，因此算的时候只筛选出4.1日的数据）

3.4 完整SQL代码

with t as(
    # 去重操作
    select cuid, event_day
    from tb_baidu
    group by cuid, event_day
), t1 as (
    # 自连接，求出差值字段
    select
        t1.cuid,
        t1.event_day a_event_day,
        t2.event_day b_event_day,
        datediff(t2.event_day,t1.event_day) diff # 差值字段
    from t t1
    join t t2
        # 自连接条件
        on t1.cuid = t2.cuid and datediff(t2.event_day,t1.event_day) >= 0
    order by a_event_day,diff,cuid
)
select
    # 就散相关指标
    a_event_day,
    count(cuid) uv,
    count(if(diff=1,cuid,null)) uv_1,
    concat(round(count(if(diff=1,cuid,null)) / count(cuid), 2) * 100 ,'%') as rate_1,
    count(if(diff=7,cuid,null)) uv_7,
    concat(round(count(if(diff=7,cuid,null)) / count(cuid), 2) * 100 ,'%') as rate_7
from t1
where a_event_day = '2020-04-01' # 筛选出4.1日的数据
group by a_event_day
;

4. 面试真题二：

4.1 准备工作

drop database if exists db_1;

create database db_1;

use db_1;

create table if not exists db_1.tb_user_action(
    user_id varchar(32),
    app_name varchar(32),
    startup_time int,
    startup_count int,
    login_date varchar(32)
)
;

insert into db_1.tb_user_action values('01', '相机', 1, 2, '2018-05-01');
insert into db_1.tb_user_action values('01', '相机', 1, 2, '2018-05-02');
insert into db_1.tb_user_action values('01', '相机', 1, 2, '2018-05-04');
insert into db_1.tb_user_action values('01', '相机', 1, 2, '2018-05-07');
insert into db_1.tb_user_action values('02', '微信', 2, 3, '2018-05-02');
insert into db_1.tb_user_action values('03', '美团', 4, 2, '2018-05-03');
insert into db_1.tb_user_action values('04', '微信', 6, 3, '2018-05-01');
insert into db_1.tb_user_action values('05', '相机', 3, 1, '2018-05-03');
insert into db_1.tb_user_action values('05', '相机', 3, 1, '2018-05-04');
insert into db_1.tb_user_action values('05', '相机', 3, 1, '2018-05-06');
insert into db_1.tb_user_action values('06', '相机', 2, 3, '2018-05-01');
insert into db_1.tb_user_action values('07', '相机', 2, 2, '2018-05-02');
insert into db_1.tb_user_action values('08', '美团', 1, 1, '2018-05-01');
insert into db_1.tb_user_action values('09', '微信', 3, 2, '2018-05-02');
insert into db_1.tb_user_action values('10', '相机', 4, 3, '2018-05-03');
insert into db_1.tb_user_action values('10', '相机', 4, 3, '2018-05-04');
insert into db_1.tb_user_action values('10', '相机', 4, 3, '2018-05-06');
insert into db_1.tb_user_action values('10', '相机', 4, 3, '2018-05-10');

select * from db_1.tb_user_action;

4.2 目标

手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。

字段说明：

用户 id：用户唯一标识；
应用名称：是手机中的某个应用，例如相机、微信、大众点评等。
启动时长：某一天中使用某应用多长时间（分钟）。
启动次数：某一天中启动了某应用多少次。
登陆时间：使用手机的日期。例如 2018-05-01。

现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：

某日活跃用户（用户 id）在后续的一周内的留存情况（计算次日留存用户数，3 日留存用户数，7 日留存用户数）

指标定义：

某日活跃用户数，某日活跃的去重用户数。
N 日活跃用户数，某日活跃的用户数在之后的第 N 日活跃用户数。
N 日活跃留存率，N 日留存用户数 / 某日活跃用户数

例：登陆时间（20180501 日）去重用户数 10000，这批用户在 20180503 日仍有 7000 人活跃，则 3 日活跃留存率为 7000/10000=70%

所需获得的结果格式如下：

a_login_date	dangtian	ciri	sanri	qiri	ciri_rate	sanri_rate	qiri_rate
2018-05-01	2	1	1	0	0.5	0.5	0
2018-05-02	2	0	0	0	0	0	0
2018-05-03	2	2	2	1	1	1	0.5
2018-05-04	3	0	1	0	0	0.3333	0
2018-05-06	2	0	0	0	0	0	0
2018-05-07	1	0	0	0	0	0	0
2018-05-10	1	0	0	0	0	0	0

4.3 求解思路

按照通用解题思路进行解题

1、每一条数据记录的是某用户某一天中使用某应用的时长和使用次数等信息。因此不用去重操作，不过题目要求统计相机应用的相关指标，因此要先筛选相机相关的数据。

2、自连接，求差值字段

3、聚合求相关指标。

4.4 完整SQL代码

with t1 as (
    # 不会存在重复数据，因此不需要去重，只需要筛选相关数据
    select user_id, login_date
    from tb_user_action
    where app_name = '相机'
), t2 as (
    # 自连接
    select a.user_id,
        a.login_date a_login_date,
        b.login_date b_login_date,
        datediff(b.login_date, a.login_date) diff
    from t1 a join t1 b
        on a.user_id = b.user_id and datediff(b.login_date, a.login_date) >= 0
    order by a_login_date, diff, user_id
)
# 计算相关指标字段
select a_login_date,
       count(if(diff = 0, user_id, null)) dangtian,
       count(if(diff = 1, user_id, null)) ciri,
       count(if(diff = 3, user_id, null)) sanri,
       count(if(diff = 7, user_id, null)) qiri,
       count(if(diff = 1, user_id, null)) / count(if(diff = 0, user_id, null)) ciri_rate,
       count(if(diff = 3, user_id, null)) / count(if(diff = 0, user_id, null)) ciri_rate,
       count(if(diff = 7, user_id, null)) / count(if(diff = 0, user_id, null)) ciri_rate
from t2
group by a_login_date
order by a_login_date
;