sql计算每日新增用户、及留存率指标

show databases;
-- 选择数据库进行建库
use tempt2022;
-- 用户注册表
create table user_info(user_id varchar(10) primary key,reg_time datetime);

insert into user_info values
('u_01','2020-01-01 09:15:00'),
('u_02','2020-01-01 00:04:00'),
('u_03','2020-01-01 22:16:00'),
('u_04','2020-01-01 20:32:00'),
('u_05','2020-01-01 13:59:00'),
('u_06','2020-01-01 21:28:00'),
('u_07','2020-01-01 14:03:00'),
('u_08','2020-01-01 11:00:00'),
('u_09','2020-01-01 23:57:00'),
('u_10','2020-01-01 04:46:00'),
('u_11','2020-01-02 14:21:00'),
('u_12','2020-01-02 11:15:00'),
('u_13','2020-01-02 07:26:00'),
('u_14','2020-01-02 10:34:00'),
('u_15','2020-01-02 08:22:00'),
('u_16','2020-01-02 14:23:00'),
('u_17','2020-01-03 09:20:00'),
('u_18','2020-01-03 11:21:00'),
('u_19','2020-01-03 12:17:00'),
('u_20','2020-01-03 15:26:00');

-- 登陆日志表
create table login_log(user_id varchar(10),login_time datetime,primary key(user_id,login_time));

insert into login_log values
('u_02','2020-01-02 00:14:00'),
('u_10','2020-01-02 08:32:00'),
('u_03','2020-01-02 09:20:00'),
('u_08','2020-01-02 10:07:00'),
('u_04','2020-01-02 10:29:00'),
('u_09','2020-01-02 11:45:00'),
('u_05','2020-01-02 12:19:00'),
('u_01','2020-01-02 14:29:00'),
('u_15','2020-01-03 00:26:00'),
('u_14','2020-01-03 11:18:00'),
('u_11','2020-01-03 13:18:00'),
('u_16','2020-01-03 14:33:00'),
('u_06','2020-01-04 07:51:00'),
('u_18','2020-01-04 08:11:00'),
('u_07','2020-01-04 09:27:00'),
('u_10','2020-01-04 10:59:00'),
('u_20','2020-01-04 11:51:00'),
('u_03','2020-01-04 12:37:00'),
('u_17','2020-01-04 15:07:00'),
('u_08','2020-01-04 16:35:00'),
('u_01','2020-01-04 19:29:00'),
('u_14','2020-01-05 08:03:00'),
('u_12','2020-01-05 10:27:00'),
('u_15','2020-01-05 16:33:00'),
('u_19','2020-01-06 09:03:00'),
('u_20','2020-01-06 15:26:00'),
('u_04','2020-01-08 11:03:00'),
('u_05','2020-01-08 12:54:00'),
('u_06','2020-01-08 19:22:00'),
('u_13','2020-01-09 10:20:00'),
('u_15','2020-01-09 16:40:00'),
('u_18','2020-01-10 21:34:00');

show tables;
select * from  user_info limit 10; 

# 每日新增DNU，次日留存率，3日留存率，7日留存率

select  *  
from user_info a 
left join login_log b on a.user_id = b.user_id;

/*
 * 次日留存：1月1日注册的新用户，在1月2日登陆了app，即登陆日期 - 注册日期 = 1天
 * 3日留存：1月1日注册的新用户，在1月4日登陆了app，即登陆日期 - 注册日期 = 3天
 * 7日留存：1月1日注册的新用户，在1月8日登陆了app，即登陆日期 - 注册日期 = 7天
 * 
 */

select  date(reg_time) as dt
       ,count(distinct a.user_id) as 新增用户数
       ,sum(datediff(login_time,reg_time) = 1) as 次日留存用户数
       ,sum(datediff(login_time,reg_time) = 3) as 三日留存用户数
       ,sum(datediff(login_time,reg_time) = 7) as 七日留存用户数
       ,concat(round(sum(datediff(login_time,reg_time) = 1)/ count(distinct a.user_id)*100,0),'%') as 次日留存率
       ,concat(round(sum(datediff(login_time,reg_time) = 3)/ count(distinct a.user_id)*100,1),'%') as 三日留存率
       ,concat(round(sum(datediff(login_time,reg_time) = 7)/ count(distinct a.user_id)*100,2),'%') as 七日留存率
from user_info a 
left join login_log b on a.user_id = b.user_id
group by date(reg_time);


/*
思考说明：
-- 因为每一个用户同一天可登录多次,需要综合业务逻辑关系，以及对数据进行去重操作；
-- 适用于：reg_time(用户信息表：记录需要唯一),login_time(用户登录信息表：需要加工成每天最早或最晚的记录) ;

*/