牛客SQL编程SQL65-SQL71

SQL65.现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
解题思路：第一步排除表中黑名单用户，用流程控制语句和sum函数求失败的次数，再除以发送的总次数

select date,format(sum(if(type="no_completed",1,0))/count(type),3)
from email e
join user u1 on e.send_id=u1.id
join user u2 on e.receive_id=u2.id
where u1.is_blacklist=0 and u2.is_blacklist=0
group by date
order by date;

SQL66.牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天
解题思路：把表按user_id分组，查找出最大的时间，即用户最近登录是哪一天，再进行排序就可以了

select user_id,max(date) d
from login
group by user_id
order by user_id;

SQL67.牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.
解题思路：连接三个表，用子查询查出用户和最近登陆时间，根据这两个字段查出其他字段
此处踩了一个坑，select u.name u_n,c.name c_n,max(date),返回的前两个字段并没与和后一个字段相对应

select u.name u_n,c.name c_n,date
from login l
join user u on l.user_id=u.id
join client c on l.client_id=c.id
where (l.user_id,date) in (select user_id,max(date) from login group by user_id)
order by u_n;

SQL68.牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
解题思路：牛客新登录用户的次日成功留存率计算公式为（首日登陆并且第二天也登陆的用户数）/（总用户数）

select format(count(distinct user_id)/(select count(distinct user_id) from login),3)
from login
where (user_id,date) in (
                            --所有首日登陆并且第二天也登陆的用户，以及日期。
                            select user_id,date_add(min(date),interval 1 day)
                            from login
                            group by user_id
                        );

SQL69.牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数
解题思路：当前日期等于一个用户登陆的日期最小值，当天这个用户就是新用户

select distinct date,sum(case when (user_id,date)
                          in (select user_id,min(date) from login group by user_id)
                             then 1 else 0 end)
from login
group by date
order by date;

SQL70.牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
解题思路：先查找出每天新登录用户，再查出第二天是否登录，算出留存率，另外查出没有新用户登录的记录，用union做并集
此处有个坑，order by 后面必须是f_date，如果是date，会报错

select new.f_date,format(count(distinct login.user_id)/count(new.user_id),3) as p
from (select user_id,min(date) as f_date from login group by user_id) as new
left join login
on new.user_id=login.user_id and login.date=date_add(new.f_date,interval 1 day)
group by new.f_date
UNION
select date as f_date,0.000 as p
from login
where date not in (select min(date) from login group by user_id)
order by f_date;

SQL71.牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
解题思路：使用自连接，按日期，姓名分组，统计累积通过的题的数量，再连接user表，查出姓名
注意一点，统计的应该是p2.number的累加和，我之前一直写成p1.number，对自连接求累加和理解错误，日期和求和应该是在不同的两列上。一个在p1，一个在p2

select u.name as u_n,p1.date,sum(p2.number) as ps_num
from passing_number p1 inner join passing_number p2
on p1.user_id=p2.user_id and p1.date>=p2.date
inner join user u
on p1.user_id=u.id 
group by p1.date,u_n
order by p1.date,u_n

也可以使用窗口函数，实现分组求和
over(partition by 字段1 order by 字段二) 按字段一分区，然后在组内按字段二排序
窗口函数的形式是 函数() + over() 但执行顺序是 over () --函数（）

select u.name u_n,p.date date,
sum(p.number) over(partition by p.user_id order by date) ps_num
from passing_number p join user u
on p.user_id=u.id
group by u_n,p.date
order by p.date,u_n;