SQL业务场景查询

场景一

邮件表记录了某邮件App的数据，包括：编号（主键）、寄信人编号、收信人编号、枚举类型（completed表示邮件发送成功，no_completed表示邮件发送失败）
创建表格

create table 邮件表
(id int not null,
sender_id int not null,
receiver_id int not null,
type varchar(20) not null,
date datetime not null)

插入数据

insert into 邮件表 values
(1,2,3,'completed','2020-01-11'),
(2,1,3,'completed','2020-01-11'),
(3,1,4,'uncompleted','2020-01-11'),
(4,3,1,'completed','2020-01-12'),
(5,3,4,'completed','2020-01-12'),
(6,4,1,'completed','2020-01-12')

用户表里记录了该邮件App的所有用户信息。其中，用户编号为主键，是否为黑名单（值为0表示是正常用户，值为1表示是黑名单用户）
创建表格

create table 用户表
(user_id int not null,
feature int )

插入数据

insert into 用户表 values
(1,0),
(2,1),
(3,0),
(4,0);

– 每一个日期里，正常用户发送给正常用户邮件失败的概率是多少？

select a.date, 
sum(case when type='completed' then 1 else 0 end)/count(type) as rate
from 邮件表 a
left join 用户表 b 
on a.sender_id=b.user_id 
left join 用户表 c
on a.receiver_id=c.user_id
where b.feature=0 and c.feature=0
group by a.date

场景二

-1用户活跃模型表

create table tmp_liujg_dau_based
(
imp_date varchar(20) not null comment '日期',
qimei  varchar(20) not null comment '用户唯一标识',
is_new  varchar(10) comment '新用户表示，1表示新用户，0表示老用户',
primary key(imp_date,qimei));
ALTER TABLE tmp_liujg_dau_based COMMENT '用户活跃模型表';

用户活跃模型表数据导入

insert into tmp_liujg_dau_based values
('20190301','001','0'),
 ('20190301','002','0'),
 ('20190301','003','1'),
 ('20190301','004','1'),
 ('20190301','005','1'),
 ('20190301','006','1'),
('20190302','001','0'),
('20190302','002','0'),
('20190302','003','0'),
('20190302','005','0'),
('20190302','006','0'),
('20190302','007','1'),
('20190303','005','0'),
('20190303','006','0'),
('20190303','007','0'),
('20190303','008','1'),
('20190303','009','1'),
('20190303','010','1'),
('20190401','008','0'),
('20190401','009','0'),
('20190401','010','0')</