SQL257 刷题通过的题目排名
- row_number() 是没有重复值的排序(即使两个记录相等也是不重复的),可以利用它来实现分页,如1,2,3,4
- dense_rank() 是连续排序,两个第二名仍然跟着第三名,如1,2,2,3
- rank() 是跳跃排序,两个第二名下来就是第四名,如1,2,2,4
select
id,
number,
dense_rank()over(order by number desc) as t_rank
from passing_number
order by `number`desc,id
SQL258 找到每个人的任务
- on a.id=b.person_id,大坑!
select a.id,a.name,b.content
from person as a
left join task as b
on a.id=b.person_id
order by a.id
SQL259 异常的邮件概率
- 第一个表里面的id就是序号,第二个表里面的id是用户id。所以这两个id不能直接连接,而是用send_id和receive_id与表二连接。
select
a.date,
round(sum(case when type='completed' then 0 else 1 end)/count(*),3) as p
from email as a
join user as b on a.send_id=b.id
join user as c on a.receive_id=c.id
where send_id !=2 and receive_id !=2
group by `date`
order by `date`
SQL260 牛客每个人最近的登录日期(一)
select
user_id,
max(date) as id
from login
group by user_id
order by user_id
SQL261 牛客每个人最近的登录日期(二)
- 如果只聚合u_n 很容易会出现full group by错误,因此可以用where语句筛选出已经聚合过的函数
select
b.name as u_n,
c.name as c_n,
a.date as date
from login as a
left join user as b on a.user_id=b.id
left join client as c on a.client_id=c.id
where (a.user_id,a.date) in
(select user_id,max(date) from login group by user_id)
order by u_n
SQL262 牛客每个人最近的登录日期(三)
- 留存率=第一天登录且第二天依旧登录的用户/用户总数
- 用户总数 再单独做一个查询就可以得到
- 第一天登录,且第二天登录,可以用date_add(字段,interval 1
day)如果第一天+1天存在,那就说明用户留存了,不存在就说明没有留存。
select
round(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
)
SQL263 牛客每个人最近的登录日期(四)
- 当某天没有新增的时候,new需要显示为0,因此不能像前几题那样直接在where里设置条件筛选,那样就只会显示有新增的天数了
- 当把case中的null换成0的时候,数字就会发生变化。因为是用count计数的,0也会记成一个数。
select
date,
count(distinct (case when (user_id,date) in
(select user_id,min(`date`) as date from login group by user_id)
then user_id else null end )) as new
from login
group by date
order by date
SQL264 牛客每个人最近的登录日期(五)
- 问题:每个日期 新用户 的次日留存 率 – 关键问题 结果保留小数点后面3位数(3位之后的四舍五入) --次要问题
并且查询结果按照日期升序排序 – 次要问题 - 思路:每个日期的新用户数–分母 每个日期的新用户次日留存–分子 求比率 (/)
- 我们要用第一次登录(第3个select后)的和第二天依旧登录的(第4个表 login)
相除求留存率,但是可能存在分母为0的情况,所以要用ifnull。 - 因为不是每天都有新用户登录,但是题目要求统计每个日期的次日留存率,所以需要用原表取一下时间(第2个select)
select t0.date,
ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)
from
(
select date
from login
group by date
) t0
left join
(
select user_id,min(date) as date
from login
group by user_id
)t1
on t0.date=t1.date
left join login as t2
on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
group by t0.date
SQL265 牛客每个人最近的登录日期(六)
- 因为有登录没有刷题的数据不需要输出,所以截止到某天指的是刷题表有记录的日期。没有用到登录表。
用窗口函数按姓名分区按日期排序计算累计数。
select
b.name as u_n,
a.date,
sum(a.number)over(partition by a.user_id order by a.date) as ps_num
from passing_number as a
join user as b on a.user_id=b.id
order by a.date,u_n