题目一:2016年的投资
题目要求:
编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和:
他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同(也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同)。
tiv_2016 四舍五入的 两位小数 。
表结构:
运行结果示例:
思路:
这道题题目描述一大串,我们首先先来分解一下题目。题目要求我们找到投保人的投保金额之和,也就是用sum()函数实现,同时要求tiv_2016保留四舍五入的两位小数,那么我们很容易就能写出select语句中的内容select round(sum(tiv_2016),2) as tiv_2016。
接下来我们处理筛选条件部分。题目要求我们所使用的数据必须满足两个条件:
1)在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同
2)所在的城市必须与其他投保人都不同(也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同)。
对于这两个条件,我们考虑用in关键字结合子查询实现。首先要求2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同,那么我们只需要找到2015年投保额相同的额度是多少即可,也就是按照保额分组,统计数量,筛选出数量大于等于2的数据,并判断主查询中的tiv_2015是否在我们查询中的结果集中。对与所在城市的判定与投保额相同,我们按照经纬度分组,找到只有一条数据的组即可。
运行代码示例:
select round(sum(tiv_2016),2) as tiv_2016
from Insurance
where tiv_2015 in (
select tiv_2015 from Insurance group by tiv_2015 having count(*) > 1
)
and (lat,lon) in (
select lat,lon from Insurance group by lat,lon having count(*)=1
)
题目二:部门工资前三高的所有员工
题目要求:
公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案,找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题的难点在于如何“按照部门分类”找到每个部门前三高的工资。其实很多人都会钻牛角尖,想要找到部门id和对应的前三工资数额,再使用in来匹配结果(没错说的就是我)。但是换一个思路,我们可以找“工资比这些员工高的不到3个”,。这种情况下,处理起来就简单多了,我们只需要使用子查询即可。考虑到效率问题,我们可以用自连接改进一下,我们找到同一个部门中,表1比表2更高的工资数据,再连接表Department,获取对应部门id的部门名称,最后选出count(distinct e2.salary)的结果小于等于3的数据即可。
运行代码示例:
select
d.name as department, e1.name as employee, e1.salary as salary
from department d
join employee e1 on d.id = e1.departmentid
join employee e2 on e1.departmentid = e2.departmentid and e1.salary<=e2.salary
group by
d.name, e1.name
having
count(distinct e2.salary)<=3
题目三:指定日期的产品价格
题目要求:
编写一个解决方案,找出在
2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是10
。以 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
本题如果直接解决,会比较复杂,整体上我们可以考虑使用子查询或者窗口函数来解决,并不难,细心就好。这里我们提供一个较为简单的思路,使用union连接查询结果。
我们可以看出,题目中存在两种情况,第一种是在2019-08-16这个时间之前有价格数据的,我们以最新数据为准,另一种是在该节点之前没有价格数据的,我们按照初始价格为10来考虑。我们直接分别查询两种情况的结果,使用union连接即可。
对于第一种情况,我们可以分组统计找到每个产品在2019-08-16之前的最后一次修改时间,再去查询对应的价格数据。第二种情况则更为简单,我们只需要找到在2019-08-16之前没有出现在表里的数据。
运行代码示例:
select product_id ,new_price as price
from Products
where (product_id,change_date) in (select product_id,max(change_date) from Products where change_date <= '2019-08-16' group by product_id )
union
select product_id,10 as price
from Products
where product_id not in (select product_id from Products where change_date <= '2019-08-16')
题目四:最后一个能进入巴士的人
题目要求:
有一队乘客在等着上巴士。然而,巴士有
1000
千克 的重量限制,所以其中一部分乘客可能无法上巴士。编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客,并报告
person_name
。题目测试用例确保顺位第一的人可以上巴士且不会超重。
表结构:
运行结果示例:
思路:
本题看似麻烦,实际上考察了窗口函数的使用,窗口函数也是SQL中常见的考点,但是很多学校的课程不教授这部分,大家可以参考【SQL】一张学生表带你学会开窗函数这篇博客中的讲解来学习。
本题的大致思路就是根据顺序(turn)开窗计算重量之和,之后筛选出所有重量和低于1000千克的乘客,也就是所有能够上车的乘客。在这些乘客中,我们按照排队顺序排序,找到最后一个即可。
运行代码示例:
select person_name
from(
select turn,person_name,sum(weight) over(order by turn) as cumu_weight from Queue
) t
where cumu_weight <= 1000
order by turn desc
limit 1
题目五:按分类统计薪水
题目要求:
查询每个工资类别的银行账户数量。 工资类别如下:
"Low Salary":所有工资 严格低于 20000 美元。
"Average Salary": 包含 范围内的所有工资 [$20000, $50000] 。
"High Salary":所有工资 严格大于 50000 美元。结果表 必须 包含所有三个类别。 如果某个类别中没有帐户,则报告 0 。
按 任意顺序 返回结果表。
表结构:
运行结果示例:
思路:
我们分别按照统计规律筛选出对应的标签和数量,再进行合并结果集即可。
运行代码示例:
select 'Low Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income < 20000
union
select 'Average Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income <= 50000 and income >= 20000
union
select 'High Salary' as category,ifnull(count(*),0) as accounts_count
from Accounts
where income > 50000