精华内容
下载资源
问答
  • 计算每日次日留存率 原数据表:user_login_table表 表字段:用户、登陆日期 sql查询: 计算用户是否是次留用户 select t1.user_name,max(case when datediff(day,date(newdate),date(logindate)) = 1 then 1 ...

    问题:

    1. 计算用户是否是次留用户
    2. 计算每日次日留存率

    原数据表:user_login_table表

    表字段:用户、登陆日期
    在这里插入图片描述

    sql查询:

    1. 计算用户是否是次留用户
    select t1.user_name,max(case when datediff(day,date(newdate),date(logindate)) = 1 then 1 else 0 end) as 是否次留用户
    from 
    (
    	select user_name,min(logindate) newdate
    	from user_login_table
    	group by user_name
    ) t1 join user_login_table t2 on t1.user_name = t2.user_name 
    group by t1.user_name
    

    查询结果:
    在这里插入图片描述
    2. 计算每日次日留存率

    select t1.logindate 日期,count(distinct case when datediff(day,date(newdate),date(t3.logindate)) = 1 then t3.user_name else null end) 次日留存用户数,count(distinct t2.user_name) 新增用户数,
    bi_division(次日留存用户数*100,新增用户数,2) || '%' "次日留存率"
    from 
    (
    	select distinct logindate 
    	from user_login_table
    ) t1 left join 
    (
    	select user_name,min(logindate) newdate
    	from user_login_table
    	group by user_name
    ) t2 on t2.newdate = t1.logindate 
    left join user_login_table t3 on t2.user_name = t3.user_name 
    group by t1.logindate
    

    查询结果:
    在这里插入图片描述
    如果以上sql中t1、t2、t3表之间任意一处用join而非left join(体现join与left join区别),读者可亲自尝试,查询结果如下:
    在这里插入图片描述

    展开全文
  • 计算新登陆用户次日的留存率、每个日期登陆的新用户数、 每个日期新用户的次日留存率1. 请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四...

    1. 请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

    select round(count(t2.user_id)*1.0/count(t1.user_id),3)
    from (select user_id,min(date) as date from login group by user_id) t1
    left join login t2
    on t1.user_id = t2.user_id and t2.date = date_add(t1.date,interval 1 day)
    

    2. 请你写出一个sql语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序,

    -- 方法1
    select tmp.date,sum(case tmp.rk when 1 then 1 else 0 end) as new
    from (select user_id,date,rank() over(partition by user_id order by date) as rk from login) tmp
    group by tmp.date
    -- 方法2
    select tmp.date,count(distinct tmp.user_id) as new
    from (select user_id,min(date) date from login group by user_id) tmp
    group by tmp.date
    union 
    select date,0 as new  from login
    where date not in (select min(date) from login group by user_id)
    order by date;
    
    

    3.请你写出一个sql语句查询每个日期新用户的次日留存率,结果保留小数点后面3位数(3位之后的四舍五入),并且查询结果按照日期升序排序

    select t1.date,round(count(distinct t2.user_id)/count(distinct t1.user_id),3) p
    from (select user_id,min(date) date from login group by user_id) t1
    left join login t2 
    on t1.user_id = t2.user_id and t2.date=date_add(t1.date,interval 1 day)
    group by t1.date
    union
    select date,0.000 p from login
    where date not in (select min(date) from login group by user_id)
    order by date;
    

    4. 计算用户连续登录天数

    create table user_login(
        user_id varchar(100),
        login_time datetime
    );
    
    insert into user_login values
    (1,'2016-11-25 13:30:45'),  
    (1,'2016-11-24 13:30:45'),  
    (1,'2016-11-24 10:30:45'),  
    (1,'2016-11-24 09:30:45'),  
    (1,'2016-11-23 09:30:45'),  
    (1,'2016-11-10 09:30:45'),  
    (1,'2016-11-09 09:30:45'),  
    (1,'2016-11-01 09:30:45'),  
    (1,'2016-10-31 09:30:45'),  
    (2,'2016-11-25 13:30:45'),  
    (2,'2016-11-24 13:30:45'),  
    (2,'2016-11-23 10:30:45'),  
    (2,'2016-11-22 09:30:45'),  
    (2,'2016-11-21 09:30:45'),  
    (2,'2016-11-20 09:30:45'),  
    (2,'2016-11-19 09:30:45'),  
    (2,'2016-11-02 09:30:45'),  
    (2,'2016-11-01 09:30:45'),  
    (2,'2016-10-31 09:30:45'),  
    (2,'2016-10-30 09:30:45'),  
    (2,'2016-10-29 09:30:45'); 
    
    1. 一天中可能有多次打卡记录
    create table user_login_date(
        select distinct user_id, date(login_time) login_date from user_login
    );
    2. 按照用户分区 login_date 从小到大 排序
    select user_id,login_date,row_number() over(partition by user_id order by login_date) rk
    from user_login_date;
    3. 计算辅助列 login_date-rk
    select user_id,login_date,rk,date_sub(login_date,interval rk day) t_date
    from(
      select user_id,login_date,row_number() over(partition by user_id order by login_date) rk
      from user_login_date
    )a
    4. 按照用户和辅助列分组 计算个数
    select user_id,min(login_date) start_date,max(login_date) end_date,count(t_date) c
    from(
      select user_id,login_date,rk,date_sub(login_date,interval rk day) t_date
      from(
        select user_id,login_date,row_number() over(partition by user_id order by login_date) rk
        from user_login_date
      )a
    )b
    group by user_id,t_date;
    +---------+------------+----+------------+
    | user_id | login_date | rk | t_date     |
    +---------+------------+----+------------+
    | 1       | 2016-10-31 |  1 | 2016-10-30 |
    | 1       | 2016-11-01 |  2 | 2016-10-30 |
    | 1       | 2016-11-09 |  3 | 2016-11-06 |
    | 1       | 2016-11-10 |  4 | 2016-11-06 |
    | 1       | 2016-11-23 |  5 | 2016-11-18 |
    | 1       | 2016-11-24 |  6 | 2016-11-18 |
    | 1       | 2016-11-25 |  7 | 2016-11-18 |
    | 2       | 2016-10-29 |  1 | 2016-10-28 |
    | 2       | 2016-10-30 |  2 | 2016-10-28 |
    | 2       | 2016-10-31 |  3 | 2016-10-28 |
    | 2       | 2016-11-01 |  4 | 2016-10-28 |
    | 2       | 2016-11-02 |  5 | 2016-10-28 |
    | 2       | 2016-11-19 |  6 | 2016-11-13 |
    | 2       | 2016-11-20 |  7 | 2016-11-13 |
    | 2       | 2016-11-21 |  8 | 2016-11-13 |
    | 2       | 2016-11-22 |  9 | 2016-11-13 |
    | 2       | 2016-11-23 | 10 | 2016-11-13 |
    | 2       | 2016-11-24 | 11 | 2016-11-13 |
    | 2       | 2016-11-25 | 12 | 2016-11-13 |
    +---------+------------+----+------------+
    结果
    +---------+------------+------------+---+
    | user_id | start_date | end_date   | c |
    +---------+------------+------------+---+
    | 1       | 2016-10-31 | 2016-11-01 | 2 |
    | 1       | 2016-11-09 | 2016-11-10 | 2 |
    | 1       | 2016-11-23 | 2016-11-25 | 3 |
    | 2       | 2016-10-29 | 2016-11-02 | 5 |
    | 2       | 2016-11-19 | 2016-11-25 | 7 |
    +---------+------------+------------+---+
    

    5. 查询连续登陆5天的用户

    1.采用lead函数 找到用户第五次登陆的日期
    select *,lead(login_date,5) over(partition by user_id order by login_date) 5th_day
    from user_login_date;
    
    +---------+------------+------------+
    | user_id | login_date | 5th_day    |
    +---------+------------+------------+
    | 1       | 2016-10-31 | 2016-11-24 |
    | 1       | 2016-11-01 | 2016-11-25 |
    | 1       | 2016-11-09 | NULL       |
    | 1       | 2016-11-10 | NULL       |
    | 1       | 2016-11-23 | NULL       |
    | 1       | 2016-11-24 | NULL       |
    | 1       | 2016-11-25 | NULL       |
    | 2       | 2016-10-29 | 2016-11-19 |
    | 2       | 2016-10-30 | 2016-11-20 |
    | 2       | 2016-10-31 | 2016-11-21 |
    | 2       | 2016-11-01 | 2016-11-22 |
    | 2       | 2016-11-02 | 2016-11-23 |
    | 2       | 2016-11-19 | 2016-11-24 |
    | 2       | 2016-11-20 | 2016-11-25 |
    | 2       | 2016-11-21 | NULL       |
    | 2       | 2016-11-22 | NULL       |
    | 2       | 2016-11-23 | NULL       |
    | 2       | 2016-11-24 | NULL       |
    | 2       | 2016-11-25 | NULL       |
    +---------+------------+------------+
    
    2. 计算第五次登陆时间和本次登陆时间相差5天的用户
    select distinct user_id 
    from(
    	select *,timestampdiff(day,login_date,5th_date) diff_date
      from (
        select *,lead(login_date,5) over(partition by user_id order by login_date) 5th_date
        from user_login_date
      )a
    )b 
    where diff_date=5;
    结果
    +---------+
    | user_id |
    +---------+
    | 2       |
    +---------+
    
    展开全文
  • 现在要计算用户次日留存率、三日留存率、七日留存率等。在计算之前,我们先来弄清楚这些留存的定义。 次日留存:即当日登录后,第二天也登录的用户,称为次日留存用户。 三日留存:即当日登录后,第三天

    本篇博客学习如何用SQL来实现次日、三日及七日留存率的计算。

    假设有一个表 role_login_back,有字段:event_time表示登陆日期时间和device_id表示设备ID。

    event_time device_id
    2020-01-01 12:40:44 cB789hj888888

    现在要计算用户的次日留存率、三日留存率、七日留存率等。在计算之前,我们先来弄清楚这些留存的定义。

    • 次日留存:即当日登录后,第二天也登录的用户,称为次日留存用户。
    • 三日留存:即当日登录后,第三天也登录的用户,称为三日留存用户。
    • 七日留存:即当日登录后,第七天也登录的用户,称为七日留存用户。

    代码如下:

    ① 将device_id分组,每个device_id按照时间进行排序。

    select 
    	distinct date(event_time) as log_day, # 只关心日期,不关注具体的时间。
    	device_id as user_id_d0
    from role_login_back
    group by device_id
    order by log_day; a
    

    ② 取出次日、第三天以及第7天登录的设备id。

    select 
    	distinct log_day,
    	a.user_id_d0,
    	b.device_id as user_id_d1,
    	c.device_id as user_id_d3,
    	d.device_id as user_id_d7
    from a
    left join role_login_back b 
    on datediff(date(b.event_time),a.log_day) = 1 
    and a.user_id_d0 = b.device_id
    left join role_login_back c 
    on datediff(date(c.event_time), a.log_day) = 2
    and a.user_id_d0 = c.device_id
    left join role_login_back d
    on datediff(date(d.event_time), a.log_day) = 6
    and a.user_id_d0 = d.device_id; temp
    

    ③ 计算次日、三日及七日留存率。

    select
    	log_day '日期',
    	count(user_id_d0) '新增数量',
    	count(user_id_d1) / count(user_id_d0) '次日留存率',
    	count(user_id_d3) / count(user_id_d0) '3日留存率',
    	count(user_id_d7) / count(user_id_d0) '7日留存率',
    from temp
    group by log_day;
    

    综合代码:

    select
    	log_day '日期',
    	count(user_id_d0) '新增数量',
    	count(user_id_d1) / count(user_id_d0) '次日留存率',
    	count(user_id_d3) / count(user_id_d0) '3日留存率',
    	count(user_id_d7) / count(user_id_d0) '7日留存率',
    from (
    	select 
    		distinct log_day,
    		a.user_id_d0,
    		b.device_id as user_id_d1,
    		c.device_id as user_id_d3,
    		d.device_id as user_id_d7
    	from 
    		(select 
    			distinct date(event_time) as log_day, # 只关心日期,不关注具体的时间。
    			device_id as user_id_d0
    		from role_login_back
    		group by device_id
    		order by log_day) a
    	left join role_login_back b 
    	on datediff(date(b.event_time),a.log_day) = 1 
    	and a.user_id_d0 = b.device_id
    	left join role_login_back c 
    	on datediff(date(c.event_time), a.log_day) = 2
    	and a.user_id_d0 = c.device_id
    	left join role_login_back d
    	on datediff(date(d.event_time), a.log_day) = 6
    	and a.user_id_d0 = d.device_id 
    	)
    group by log_day;
    
    展开全文
  • SQL计算留存率指标

    2020-03-19 21:38:21
    concat(round(100 * 次日留存用户数/活跃用户数, 2), '%') 次日留存率, concat(round(100 * 三日留存用户数/活跃用户数, 2), '%') 三日留存率, concat(round(100 * 七日留存用户数/活跃用户数, 2), '%') 七日留存...
    select *,
    	concat(round(100 * 次日留存用户数/活跃用户数, 2), '%') 次日留存率,
    	concat(round(100 * 三日留存用户数/活跃用户数, 2), '%') 三日留存率,
    	concat(round(100 * 七日留存用户数/活跃用户数, 2), '%') 七日留存率
    from (
    	select 
    		a.log_day 日期,
    		count(distinct(concat(a.device_id, a.app_id))) 活跃用户数,
    		count(distinct(concat(b.device_id, b.app_id))) 次日留存用户数,
    		count(distinct(concat(c.device_id, c.app_id))) 三日留存用户数,
    		count(distinct(concat(d.device_id, d.app_id))) 七日留存用户数
    	from user_log a
    	left join user_log b on concat(a.device_id, a.app_id) = concat(b.device_id, b.app_id) and b.log_day = a.log_day + 1
    	left join user_log c on concat(a.device_id, a.app_id) = concat(c.device_id, c.app_id) and c.log_day = a.log_day + 3
    	left join user_log d on concat(a.device_id, a.app_id) = concat(d.device_id, d.app_id) and d.log_day = a.log_day + 7
    	group by a.log_day
    	) p;

     

    展开全文
  • SQL用户留存率

    2020-12-15 16:02:20
    记录面试遇到的SQL题目--用户留存率 已知一张用户信息表。结构如下: 现需要计算日活跃用户,以及次日、3日、7日的用户留存率: 解决思路如下: 单个表既有新客户,也有老用户每天的登录时间,所以进行自联结...
  • SQL计算留存率等指标

    千次阅读 2020-01-05 23:47:46
    一、问题1:留存率计算 字段及表说明: 表名:user_log 字段名: ...1.1计算某日留存率次日、3日、7日、30日) --计算次日、3日、7日、30日留存率 select log_day,count(user_id_d1)/count(user_id...
  • 一个SQL,程序实现多日留存率计算

    千次阅读 2020-01-21 16:36:23
    留存率是衡量用户质量的最重要指标之一,因此计算用户留存率是用户数据分析中必须掌握的技能之一。 留存率指标中,通常需要关注次日留存、3日留存、7日留存和月留存。对新增用户而言,需要关注更细颗粒度的数据,也...
  • sql留存用户率练习题

    2020-07-20 22:25:55
    某日活跃用户用户id)在后续的一周内的留存情况(计算次日留存用户数,3日留存用户数,7日留存用户数) 指标定义 某日活跃用户数,某日活跃的去重用户数。 N日活跃用户数,某日活跃的用户数在之后的第N日活跃...
  • 计算次日留存用户数,3日留存用户数,7日留存用户数,及对应留存率 字段描述 uid:用户id,用户唯一标识 appname:应用名称,手机中某个应用,例如相机、QQ、微信等 usetime:启动时长,某一天中使用了多长时间,...
  • SQL语句 — 留存率

    2020-08-08 09:52:04
    假如某日新增了100个用户,第二天登录了50个,则次日留存率为50/100=50%,第三天登录了30个,则第二日留存率为30/100=30%,以此类推,第7天登录了10个用户,则7日留存率就是10/100=10%。 二、SQL实现 1、数据说明...
  • 数据运营-计算留存率等指标(SQL

    万次阅读 2019-02-02 21:48:49
    一、问题1:留存率计算 字段及表说明: 表名:user_log 字段名: ...1.1计算某日留存率次日、3日、7日、30日) --计算次日、3日、7日、30日留存率 select log_day,count(user_id_d1)/count(user_id...
  • 如何用SQL留存率分析

    千次阅读 2019-03-28 20:19:17
    背景 APP分析中经常用到AARRR模型(海盗模型)用来分析APP的...假如今天新增了100名用户,第二天登陆了50名,则次日留存率为50/100=50%,第三天登录了30名,则第二日留存率为30/100=30%,以此类推。 用SQL计算思路...
  • 用户留存sql完整版

    千次阅读 2020-06-24 12:07:52
    一般在运营或者BI报表里面,关于渠道用户分析时,用户留存是个不可缺少的过程,也是业界多渠道用户质量比较成熟的判断标准,主要指标,包括计算用户次日、3日、7日、30天、90天等的留存率,下面主要介绍sql的统计...
  • 【DA】留存率-SQL实现

    2021-04-15 12:12:58
    1 背景 留存率:是用户分析的核心指标之一。它也是经典的AARRR模型(海盗模型)中就有一个重要节点——留存(Acquisition)。留存率的计算也是用户...假如某日新增了100个用户,第二天登录了50个,则次日留存率为50/.
  • 用户七日留存率分析

    2019-04-22 10:14:33
    背景 APP分析中经常用到AARRR模型(海盗模型)用来分析APP的...假如今天新增了100名用户,第二天登陆了50名,则次日留存率为50/100=50%,第三天登录了30名,则第二日留存率为30/100=30%,以此类推。 用SQL计算思路...
  • 留存率分析

    2020-12-02 14:14:46
    背景 APP分析中经常用到AARRR模型(海盗模型)用来分析APP的...假如今天新增了100名用户,第二天登陆了50名,则次日留存率为50/100=50%,第三天登录了30名,则第二日留存率为30/100=30%,以此类推。 SQL计算思路 ...
  • 用户七日留存问题

    2020-04-18 20:05:21
    背景 APP分析中经常用到AARRR模型(海盗模型)用来分析APP的...假如今天新增了100名用户,第二天登陆了50名,则次日留存率为50/100=50%,第三天登录了30名,则第二日留存率为30/100=30%,以此类推。 用SQL计算思路...
  • 留存率是考察企业留住用户的能力,我们经常会计算次日留存、2日留存、7日留存等,如果现在让你用SQL实现,该怎么操作呢? 表部分数据如下: 留存数据字段说明: 用户id:用户唯一标识; APP应用名称:是手机中的...
  • 当我们对产品进行用户行为分析时,会比较关注留存相关的指标,我们希望用户能每天使用我们的产品,那么除了常规的计算用户次日、3日、7日...n日留存率之外,我们该怎么分析一个用户连续登陆的天数呢?我们来一步步...
  • Hive面试/经典练习题一、N日留存1....数据准备2.思路分析三、 Top N 一、N日留存 ... 次日留存:第2天,1月2日,这200人里面有100人活跃,则次日留存率为: 100 / 200 = 50% 2日留存:第3天,1月3日;这200名新
  • (第一天登录的新用户并且第二天也登录的用户)/(总用户)即为新登录用户次日成功的留存率用户计算如下: select count(distinct user_id) from login 找到每个用户第一天登陆的日子,和前面找最近登录的...

空空如也

空空如也

1 2
收藏数 25
精华内容 10
关键字:

sql计算用户次日留存率