mysql查询用户留存语法(用户留存和用户留存率问题)大厂面试必问
用户留存和用户留存率问题,小编在面对几次大厂面试的时候都问了这个题,同时在实际生产环境中也常用到这个语法.
1、创建表
CREATE TABLE `user_log` (
`device_id` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`app_id` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
`times` int(100) DEFAULT NULL,
`duration` int(100) DEFAULT NULL,
`log_day` date DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;
'''
字段及表说明:
表名:user_log
字段名:
log_day:登录日期
device_id:用户设备id
times:日登陆次数
duration:平均每次登陆时长
app_id:用户app的id,其中device_id和app_id确定唯一的用户
'''
2、插入数据
INSERT into user_log VALUES
('a001','R11',5,10,'2020/3/17'),
......
('a001','R11',5,13,'2020/3/1'),
('a005','R11',6,14,'2020/3/1');
3、查询语句:用户留存/留存率(次日、3日、7日、30日,...)
3.1计算某日用户留存率(次日、3日、7日、30日,...)
-- 3.1计算某日用户留存率(次日、3日、7日、30日,...)
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;
1.2计算某日新增用户留存率(次日、3日、7日、30日、...)
-- 1.2计算某日新增用户留存率(次日、3日、7日、30日、...)
select *,
concat(round(100*次日留存用户/日新增用户数,2),'%') 次日留存率,
concat(round(100*三日留存用户/日新增用户数,2),'%') 三日留存率,
concat(round(100*七日留存用户/日新增用户数,2),'%') 七日留存率
from
(
select
c.log_day 日期,
count(distinct concat(c.device_id,c.app_id)) 日新增用户数,
count(distinct concat(d.device_id,d.app_id)) 次日留存用户,
count(distinct concat(e.device_id,e.app_id)) 三日留存用户,
count(distinct concat(f.device_id,f.app_id)) 七日留存用户
from
(
select a.*
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
where b.log_day is null
) c
left join user_log d on concat(c.device_id,c.app_id) = concat(d.device_id,d.app_id) and d.log_day = c.log_day + 1
left join user_log e on concat(c.device_id,c.app_id) = concat(e.device_id,e.app_id) and e.log_day = c.log_day + 3
left join user_log f on concat(c.device_id,c.app_id) = concat(f.device_id,f.app_id) and f.log_day = c.log_day + 7
group by c.log_day
) p;