终极 SQL 面试准备。你永远需要它！
终极 SQL 面试准备。你永远需要它！
练习 61 道 leetcode SQL 题
小号 QL 是查询存储在大型数据库系统中的数据的最流行的语言。每个 IT 行业对 SQL 的需求都很高。根据 Stack Overflow 的 2022 年开发者调查 , SQL 是整个行业第三流行的语言。这种语言在使用数据的开发人员中特别受欢迎。根据调查，大约 70%（数据科学家、数据分析师、数据工程师等）使用 SQL，而使用 python 的比例为 61.7%。所以，公司明白 SQL 的重要性，问 SQL 问题已经成为面试过程的重要组成部分，尤其是对于上述角色。
本文将为您准备好在任何顶级公司的 SQL 面试中胜出。但是，即使您是现有的数据科学家（如我）或数据分析师等，您仍然会从练习这些问题中受益匪浅。当我意识到 SQL 是我日常任务中最常用的工具时，我学习更多关于 SQL 的旅程开始了。所以，我想更有效地解决 SQL 问题，因此我开始练习 leetcode 上的问题。
根据问题的难度，这组问题主要分为三个主要类别——
- 简单的
- 中等的
- 难的
能够尝试这些问题的先决条件是对 SQL 的语法有基本的了解，了解一些常见的语句，如 select、group by、order by 等，以及一些子句，如 limit、where、joins 等.
有一些先进的概念，如 窗口函数 , 子查询 , 自加入 ， 和 带子句 用于解决以下一些问题。我建议在开始解决问题之前先检查一遍。但是，如果您认为自己了解所有这些高级概念，请随意跳过。
为了获得面试的最佳实践，请先尝试解决这些问题，而不要查看解决方案。该链接附加到一个可在 leetcode 平台上免费访问的问题。您可以在平台上尝试问题并返回此处查看解决方案。但是，对于一些没有 leetcode premium 无法访问的问题，对于那些我已经用示例详细编写了它们。因此，您可以查看以 [docs] 结尾的问题 文档。
请原谅我，文档格式不正确，而且由于时间限制，问题的顺序也不正确。一个快速提示是使用 CTRL-F 并键入问题的编号以轻松导航到您的问题。
对于那些认为简单的问题对他们来说太简单的人，他们可以跳过简单的类型，但我绝对会建议他们尝试每一种中等类型。
简单的
175. 合并两张表
解决方案： SELECT Person.firstName , Person.lastName, Address.city, Address.state FROM Person LEFT JOIN Address ON Person.personId = Address.personId;
181. 员工收入高于经理
解决方案： SELECT e1.Name AS Employee FROM Employee e1 JOIN e2 ON
e1.ManagerId = e2.Id WHERE e1.Salary > e2.Salary;
182. 重复的电子邮件
解决方案： SELECT DISTINCT p1.email 作为来自 Person p1 的电子邮件 JOIN Person p2
ON p1.id != p2.id AND p1.email = p2.email;
183. 从不下单的顾客
解决方案： 从客户中选择名称作为客户
WHERE id NOT IN（从订单中选择 customerId）；
196.删除重复的电子邮件
解决方案： 从人 p1 中删除 p2 加入人 p2
在 p2.id > p1.id 和 p1.email = p2.email;
197. 温度上升
解决方案： 从天气 w1 中选择 w2.id 作为 ID 加入天气 w2
ON DATEDIFF(w2.recordDate,w1.recordDate) = 1
和 w2.温度 > w1.温度；
511. 游戏玩法分析Ⅰ
解决方案： SELECT player_id, min(event_date) as first_login FROM Activity
GROUP BY player_id
595个大国
解决方案： 从世界中选择名称、人口、区域
WHERE 面积 >=3000000 或人口 >= 25000000
596个班级5人以上。
解决方案： SELECT class FROM Courses GROUP BYclass
HAVING(计数(DISTINCT(学生)) >=5
620不无聊的电影
解决方案： 从电影中选择 *
WHERE (id %2 != 0) AND description != '无聊'
按等级 DESC 排序；
626 调薪
解决方案： 更新工资设置性别 = if(sex="m" "f", "m")
1068 产品销售分析 1 [文档]
解决方案： SELECT product_name, year, price FROM Sales JOIN product ON sales.product_id = product.product_id
1069 产品销售分析 2 [文档]
解决方案： SELECT product_id, SUM(quantity) as total_quantity FROM sales GROUP BY product_id
1082 销售分析 1 [文档]
解决方案： SELECT Seller_id FROM Sales GROUP BY Seller_id HAVING SUM(price) =(SELECT SUM(price) AS total_price FROM sales GROUP BYseller_id ORDER BY total_price DESC LIMIT 1)
1083 销售分析 2 [文档]
解决方案： 从 Product JOIN Sales ON Product_id = Sales.product_id 中选择买家 ID 按买家 ID 分组 HAVING SUM(product_name = "S8") > 0 AND SUM (product_name = "iphone") = 0
1084 销售分析3
解决方案： SELECT s.product_id, product_name FROM Sales s JOIN Product p
ON s.product_id = p.product_id GROUP BY s.product_id
HAVING MIN(sale_date) >='2010-01-01'
AND MAX(sale_date) <= '2019-03-31';
1179 重新格式化部门表
解决方案： 选择身份证，
SUM(IF(month = 'Jan', income, null)) as Jan_Revenue,
SUM(IF(month = 'Feb', income, null) )as Feb_Revenue,
SUM(IF(month = 'Mar', income, null) )as Mar_Revenue,
SUM(IF(month = 'Apr', income, null)) as Apr_Revenue,
SUM(IF(month = 'May', income, null)) as May_Revenue,
SUM(IF(month = 'Jun', income, null)) as Jun_Revenue,
SUM(IF(month = 'Jul', income, null)) as Jul_Revenue,
SUM(IF(month = 'Aug', income, null)) as Aug_Revenue,
SUM(IF(month = 'Sep', income, null)) as Sep_Revenue,
SUM(IF(month = 'Oct', income, null)) as Oct_Revenue,
SUM(IF(month = 'Nov', income, null)) as Nov_Revenue,
SUM(IF(month = 'Dec', income, null)) as Dec_Revenue
从部门
按 ID 分组；
1777：每个商店的产品价格 [文档]
解决方案： SELECT product_id,MAX(IF(store = 'store1' , price, null)) AS store1,
MAX(IF(store = 'store2' , price, null)) AS store2,
MAX(IF(store = 'store3' , price, null)) AS store3,
来自产品
GROUP BY product_id;
1757. 可回收和低脂产品 [文档]
解决方案： 从产品中选择 product_id，其中 low_fats = 'Y' AND recyclable ='Y'；
1571：仓库经理 [文档]
解决方案： SELECT name as warehouse, SUM(Width * Length * Height * Units) AS volume FROM warehouse JOIN Products ON warehouse.product_id = products.product_id Group by name;
1661：每台机器的平均处理时间 [docs]
解决方案： SELECT machine_id,ROUND( AVG(a.timestamp - b.timestamp),3) as processing_time FROM Activity a JOIN Activity b ON a.machine_id = b.machine_id and a.process_id = b.process_id and a.activity_type != b。 activity_type GROUP BY machine_id；
1407 顶级旅行者
解决方案： SELECT name, COALESCE(SUM(distance),0) AS travelled_distance FROM users LEFT JOIN Rides ON Users.id =Rides.user_id GROUP BY user_id ORDER BY 2 desc, 1 asc;
1741. 计算每个员工花费的总时间
解决方案： SELECT event_day as day, emp_id, sum(out_time) - sum(in_time) as total_time FROM Employees GROUP BY emp_id,event_day;
577：员工奖金 [文档]
解决方案： SELECT name, bonus FROM Employee LEFT JOIN Bonus ON Employee.empId = Bonus.empid WHERE COALESCE(bonus, 0) < 1000;
1821：找到今年收入为正的客户 [docs]
解决方案： 从收入 > 0 且年份 = 2021 的客户中选择 customer_id；
610：三角判断 [文档]
解决方案： 选择 x,y,z IF( x+ y > z AND x + z > y AND y + z >x , “Yes”,” No”) as triangle FROM triangle;
1173：立即送餐 1 [文档]
解决方案： SELECT ROUND(SUM(order_date== customer_pref_delivery_date) *100 / COUNT(*), 2) AS immediate_percentage FROM Delievery;
1350：系无效的学生 [文档]
解决方案： SELECT name, id FROM Students WHERE department_id NOT IN (SELECT id FROM Departments);
1633：参加比赛的用户百分比 [docs]
解决方案： SELECT name, id FROM Students WHERE department_id NOT IN (SELECT id FROM Departments);
586. 下单数量最多的客户
解决方案： SELECT customer_name FROM Orders GROUP BY customer_number ORDER BY COUNT(DISTINCT order_number) DESC LIMIT 1;
中等的
176. 薪金第二高
解决方案： 从员工中选择 MAX(Salary) 作为 SecondHighestSalary
WHERE Salary NOT IN (SELECT max(Salary) FROM Employee);
177.第N高薪
解决方案： 创建函数 getNthHighestSalary(N INT) 返回 INT
开始
SET N = N-1； 返回 （
从 Employee 中选择不同的薪水
按工资顺序排列
限制 1 偏移量 N
);
结尾
178. 排名分数
解决方案： SELECT score, DENSE_RANK() OVER(order by score desc) AS 'rank'
从分数;
180. 连续数
解决方案： SELECT DISTINCT a.num AS ConsecutiveNums FROM 记录 a
加入日志 b
在 a.id + 1 = b.id AND a.num = b.num
加入日志 c
ON a.id + 2 = c.id AND a.num = c.num;
184.部门最高工资
解决方案： 选择 d.name 作为部门，e1.name 作为员工，
e1.salary 作为工资
FROM Employee e1 JOIN (SELECT departmentId, max(salary) AS max_salary FROM Employee GROUP BY departmentId) e2
ON e1.departmentId = e2.departmentId
加入部门 d ON e1.departmentId = d.id
其中e1.salary = e2.max_salary；
512. 游戏玩法分析 2 [文档]
解决方案： SELECT player_id, device_id FROM(SELECT player_id, device_id, event_date, row_number() OVER (PARTITION BY player_id ORDER BY event_date) 作为“r FROM Activity) 查找
其中 r = 1
534 游戏玩法分析 3 [文档]
解决方案 1：使用自联接 选择 a.player_id, a.event_date, sum(b.games_played) 作为 games_played_so_far 从活动 a 加入活动 b 在 a.player_id = b.player_id 和 a.event_date >= b.event_date 组由 a.player_id, a.event_date 解决方案 2：使用窗口功能 选择 player_id, event_date, sum(games_played) over (partition by player_id order by event_date) as games_palyed_so_far from activity
550 游戏玩法分析 4 [文档]
解决方案 ： 选择 round(count(distinct b.player_id)/count(distinct a.player_id),2) 作为分数
(Select player_id, device_id, min(event_date) as event_date from activity group by player_id) a left join activity b on
a.player_id = b.player_id 和 a.event_date + 1 = b.event_date
570 名经理，至少有 5 名直接下属 [文档]
解决方案： 从 Employee 中选择名称，其中 id 在（通过 managerId 从员工组中选择 managerId 具有（count（distinct（Id））> = 5）
626个交换座位
解决方案： 选择 IF(id < (从座位上选择 MAX(id)),
IF(id%2 =0,id-1,id+1),
IF(id%2=0,id-1,id)) 作为 id, 学生
FROM Seat ORDER BY id
1070 产品销售分析 3 [文档]
解决方案 SELECT product_id,year AS first_year, quantity , price FROM Sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) AS year FROM Sales GROUP BY product_id)
1270 所有人都向给定的经理报告 [文档]
解决方案： SELECT a.employee_id FROM Employee a JOIN Employee b JOIN Employee c ON a.manager_id = b.employee_id AND b.manager_id = c.employee_id WHERE c.manager_id = 1 and a.employee_id !=1;
1355 位活动参与者 [文档]
解决方案： SELECT 活动, COUNT(*) FROM Friends GROUP BY 活动 HAVING COUNT(*) != (SELECT count(*) FROM Friends GROUP BY 活动 ORDER BY (*) ASC LIMIT 1) 和 count(*) != SELECT count( *) FROM Friends GROUP BY 活动 ORDER BY count(*) DESC LIMIT 1;
1364 客户的可信联系人数量 [文档]
解决方案： SELECT invoice_id, customer_name, price, COUNT(contact_email) AS contacts_cnt, SUM( IF (contact_email IN (SELECT DISTINCT email FROM CUSTOMERS),1,0) AS Truested_contacts_cnt FROM CUSTOMERS LEFT JOIN CONTACTS ON Customers.customer_id = Contacts.user_id 加入发票Customers.customer_ID = Invoices.user_id GROUP BY invoice_id ORDER BY invoice_id;
1454 活跃用户 [文档]
解决方案： SELECT DISTINCT a.id, Accounts.name FROM Logins a JOIN Logins b
ON a.id = b.id AND DATEDIFF(a.login_date, b.login_date) 介于 1 和 4 之间
在 a.id 上加入帐户 = Accounts.id
GROUP BY a.id, a.login_date HAVING COUNT(DISTINCT b.login_date) = 4 ORDER BY a.id;
1596：每个客户最常订购的产品 [文档]
解决方案： SELECT customer_id, Products.product_id, Products.product_name FROM ( SELECT customer_id, product_id, RANK () over( PARTITION BY order_id ORDER BY order_count DESC) r FROM ( SELECT customer_id, product_id , COUNT(DISTINCT order_id) AS order_count FROM Orders GROUP BY customer_id , product_id) order_count_ranked
在 order_counts_ranked.product_id = Products.product_id WHERE r = 1 时加入产品；
1715：数苹果和橙子 [文档]
解决方案： SELECT SUM(Boxes.apple_count +COALESCE( Chests.apple_count,0)) as apple_count, SUM(Boxes.orange_count + COALESCE(Chests.orange_count,0)) as orange_count FROM Boxes LEFT JOIN Chests ON Boxes.chest_id = Chests.chest_id;
1783：大满贯冠军 [文档]
解决方案： 选择 player_id, player_name, SUM(player_id = Wibledone) + SUM(player_id = Fr_open) + SUM(player_id = US_open) + SUM(player_id = Au_open) 作为grand_slam_count 来自球员加入锦标赛的球员_id = Wibledon 或 player_id = Fr_open 或球员_id = US_open OR ON player_id = Au_open GROUP BY player_id;
1501：您可以安全投资的国家 [docs]
解决方案： SELECT Country.name AS 'country' FROM Person JOIN Country ON LEFT(Person.phone_number, 3) = Country.country_code JOIN Calls ON Person.id = Caller_id OR Person.id = Callee_id GROUP BY Country.name HAVING AVG( Calls.duration ) >(SELECT AVG(duration) FROM Calls);
1393. 资本收益/损失
解决方案： SELECT stock_name, SUM(IF(operation = 'Sell' , price, 0 )) - SUM(IF(operation = Buy, price, 0 )) AS capital_gain_loss FROM stock BY stock_name;
1212：足球锦标赛中的球队得分 [文档]
解决方案： 选择团队 ID、团队名称、
SUM(IF(team_id = host_team AND host_goals > guest_goals, 3, 0 )) +
SUM(IF(team_id = guest_name AND guest_goals > host_goals , 3, 0 )) +
SUM(IF(team_id = host_team AND host_goals = guest_goals, 1, 0)) +
SUM(IF(team_id = guest_name AND host_goals = guest_goals, 1, 0 )) AS num_points FROM Teams LEFT JOIN Matches ON Teams.team_id = Matches.host_team OR Teams.team_id = Matches.guest_team GROUP BY team_id ORDER BY num_points DESC, team_id ASC ;
1468：计算工资 [文档]
解决方案： SELECT Salaries .company_id, employee_id, employee_name, ROUND(salary * (1-tax_rate)) AS 工资 FROM Salaries JOIN (SELECT company_id, IF(MAX(salary) < 1000, 0, IF(MAX(salary) > 10000, 0.49, 0.24)) AS tax_rate FROM Salaries group by company_id) 对 Salaries.company_id 征税 = tax.company_id;
1204：最后一个可以进入电梯的人 [文档]
解决方案： SELECT a.person_name, FROM Queue a JOIN Queue b ON a.turn >= b.turn GROUP BY a.turn HAVING SUM(b.weight) <= 1000 Order by a.turn DESC Limit 1;
1699：两个人之间的通话次数 [文档]
解决方案： SELECT LEAST(from_id, to_id) AS person1, GREATEST(from_id, to_id) AS person2, COUNT(*) AS call_count, SUM(duarion) AS total_duration FROM Calls GROUP BY person1, person2;
难的
185.部门前三名工资
解决方案： 选择 d.name 作为部门，e1.name 作为员工，e1.salary 作为薪水
来自员工 e1
加入 （
SELECT id, DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY Salary desc ) AS 'rr'
来自员工）e2
开 e1.id = e2.id
加入部门 d ON d.id = e1.departmentId
其中 e2.rr <4
解决方案： 选择 request_at 作为“天”，
round(SUM(IF(status!= "completed",1,0))/ Count(status),2) 作为 "取消率" 从 Trips WHERE request_at 在 "2013-10-01" AND "2013-10-03" 之间" AND client_id IN (SELECT users_id FROM Users WHERE banned = "No") AND driver_id IN (SELECT users_id FROM Users WHERE ban = "No") GROUP BY request_at
601体育场人流量
解决方案： 选择不同的 a.id、a.visit_date、a.people
FROM 体育场 a JOIN 体育场 b JOIN 体育场 c
上
（a.id + 1 = b.id 和 a.id + 2 = c.id）
或者
（a.id -1 = b.id 和 a.id - 2 = c.id）
或者
（a.id + 1 = b.id 和 a.id -1 = c.id） 其中 a.people >=100 AND b.people>=100 AND c.people>=100
ORDER BY visit_date ASC；
1097 游戏玩法分析 5 [文档]
解决方案： SELECT a.event_date AS install_dt, COUNT(a.player_id) AS installs, ROUND(COUNT( b.player_id)/COUNT(a.player_id),2) AS Day1_retension FROM(
SELECT player_id, MIN(event_date) AS event_date FROM Activity GROUP BY player_id) a
左加入活动 b
ON a.player_id = b.player_id AND a.event_date + 1 = b.event_date
按 a.event_date 分组；
1479：按星期几的销售额 [文档]
解决方案： 选择 item_category 作为类别， SUM(IF(WEEKDAY(order_date) = 0 , 数量, 0 )) 作为星期一， SUM(IF(WEEKDAY(order_date) = 1, 数量, 0 )) 作为星期二， SUM(IF(WEEKDAY(order_date) = 2, 数量, 0 )) 作为星期三， SUM(IF(WEEKDAY(order_date) = 3, 数量, 0 )) AS 星期四 SUM(IF(WEEKDAY(order_date) = 4, quantity, 0 )) AS Friday, SUM(IF(WEEKDAY(order_date) = 5, quantity, 0 )) AS 星期六， SUM(IF(WEEKDAY(order_date) = 6, 数量, 0 )) AS 星期日 FROM Items LEFT JOIN Orders ON Items.item_id = Orders.item_id GROUP BY item_category 按 item_category 订购
1369：获取第二个最近的活动 [DOCS]
解决方案： SELECT username, activity, startDate, endDate FROM (SELECT username, activity, startDate, endDate, RANK() OVER(partition by username order by endDate desc) r, COUNT() OVER(partition by username) c) FROM UserActivity) 查找 WHERE r = 2 或 c = 1；
1412：在所有考试中找到所有安静的学生 [文档]
解决方案： SELECT Students.student_id , student_name FROM Exam JOIN Student ON Exam.student_id = Students.student_id Group by student_id HAVING SUM((exam_id, score) IN (SELECT Exam_id, MIN(score) AS score FROM Exam Group byexam_id
联盟
SELECT Exam_id, MAX(score) AS score FROM GROUP BY Exam_id ) ) = 0 )ORDER BY student_id;
感谢您阅读本文！如果您有任何问题，请发表评论，如果您觉得这篇文章有帮助，请点赞。这是我的 领英个人资料 随意连接:)
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明
