SQL(1/13)

异常的邮件概率

现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;
…
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;
…
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;

下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户
。。。
第4行表示id为4的是正常用户

现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

结果表示:
2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500;
2020-01-12没有失败的情况，所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

select e.date,round(sum(case e.type 
when "no_completed" then 1 else 0 end)/count(*),3) p
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 e.date
order by date
# round函数，保留小数

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。

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

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.

select u.name as u_n, c.name as c_n, lo.date as d
from login as lo
join (
select user_id, max(date) date
    from login group by user_id
)t1
on lo.date = t1.date and lo.user_id = t1.user_id
join user u on u.id = lo.user_id
join client c on c.id = lo.client_id
order by u_n asc;

牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率

有一个登录(login)记录表，简况如下:
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:

select 
round(count(distinct user_id)*1.0/(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);

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数

有一个登录(login)记录表，简况如下:
牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:

select a.date,
sum(case when t_rank=1 then 1 else 0 end)
from (select date,row_number()over(partition by user_id order by date)as t_rank
     from login) as a
group by a.date