【牛客刷题】SQL专项错题记录四

1.牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。

有一个登录(login)记录表，简况如下:

请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

考察点：窗口函数

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

2.牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:

第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)

第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)

第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为前端岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when ...then ...else ..end函数)

考察点：case when

SELECT 
job,
CASE WHEN total%2=0 THEN total/2 ELSE (total+1)/2 END AS `start`,
CASE WHEN total%2=0 THEN total/2+1 ELSE (total+1)/2 END AS `end`
FROM
(SELECT job,COUNT(1) AS total
FROM grade
GROUP BY job)t;           

3.牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

select t.id, t.job, t.score, t.rank
from 
(
    select *, count(*) over(partition by job) as c, row_number()over(partition by job order by score desc) as rank
    from grade
) as t
where rank>=(t.c+1)/2
and rank<=(t.c+2)/2
group by id;