615. Average Salary: Departments VS Company
Attempted
Hard
Topics
SQL Schema
Pandas Schema
Table: Salary
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| employee_id | int |
| amount | int |
| pay_date | date |
+-------------+------+
In SQL, id is the primary key column for this table.
Each row of this table indicates the salary of an employee in one month.
employee_id is a foreign key (reference column) from the Employee table.
Table: Employee
+---------------+------+
| Column Name | Type |
+---------------+------+
| employee_id | int |
| department_id | int |
+---------------+------+
In SQL, employee_id is the primary key column for this table.
Each row of this table indicates the department of an employee.
Find the comparison result (higher/lower/same) of the average salary of employees in a department to the company's average salary.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Salary table:
+----+-------------+--------+------------+
| id | employee_id | amount | pay_date |
+----+-------------+--------+------------+
| 1 | 1 | 9000 | 2017/03/31 |
| 2 | 2 | 6000 | 2017/03/31 |
| 3 | 3 | 10000 | 2017/03/31 |
| 4 | 1 | 7000 | 2017/02/28 |
| 5 | 2 | 6000 | 2017/02/28 |
| 6 | 3 | 8000 | 2017/02/28 |
+----+-------------+--------+------------+
Employee table:
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
+-------------+---------------+
Output:
+-----------+---------------+------------+
| pay_month | department_id | comparison |
+-----------+---------------+------------+
| 2017-02 | 1 | same |
| 2017-03 | 1 | higher |
| 2017-02 | 2 | same |
| 2017-03 | 2 | lower |
+-----------+---------------+------------+
Explanation:
In March, the company's average salary is (9000+6000+10000)/3 = 8333.33...
The average salary for department '1' is 9000, which is the salary of employee_id '1' since there is only one employee in this department. So the comparison result is 'higher' since 9000 > 8333.33 obviously.
The average salary of department '2' is (6000 + 10000)/2 = 8000, which is the average of employee_id '2' and '3'. So the comparison result is 'lower' since 8000 < 8333.33.
With he same formula for the average salary comparison in February, the result is 'same' since both the department '1' and '2' have the same average salary with the company, which is 7000.
FROM Salary s
JOIN Employee e
ON s.employee_id = e.employee_id
然后：
GROUP BY pay_month, department_id
得到：
2017-02 department 1 7000
2017-02 department 2 7000
2017-03 department 1 9000
2017-03 department 2 8000
3. 最后比较
CASE
WHEN d.department_avg > c.company_avg THEN 'higher'
WHEN d.department_avg < c.company_avg THEN 'lower'
ELSE 'same'
END
614. Second Degree Follower
Medium
Topics
SQL Schema
Pandas Schema
Table: Follow
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| followee | varchar |
| follower | varchar |
+-------------+---------+
(followee, follower) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the user follower follows the user followee on a social network.
There will not be a user following themself.
A second-degree follower is a user who:
follows at least one user, and
is followed by at least one user.
Write a solution to report the second-degree users and the number of their followers.
Return the result table ordered by follower in alphabetical order.
The result format is in the following example.
Example 1:
Input:
Follow table:
+----------+----------+
| followee | follower |
+----------+----------+
| Alice | Bob |
| Bob | Cena |
| Bob | Donald |
| Donald | Edward |
+----------+----------+
Output:
+----------+-----+
| follower | num |
+----------+-----+
| Bob | 2 |
| Donald | 1 |
+----------+-----+
Explanation:
User Bob has 2 followers. Bob is a second-degree follower because he follows Alice, so we include him in the result table.
User Donald has 1 follower. Donald is a second-degree follower because he follows Bob, so we include him in the result table.
User Alice has 1 follower. Alice is not a second-degree follower because she does not follow anyone, so we don not include her in the result table.
613. Shortest Distance in a Line
Easy
Topics
SQL Schema
Pandas Schema
Table: Point
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
+-------------+------+
In SQL, x is the primary key column for this table.
Each row of this table indicates the position of a point on the X-axis.
Find the shortest distance between any two points from the Point table.
It is guaranteed that the Point table contains at least two rows.
The result format is in the following example.
Example 1:
Input:
Point table:
+----+
| x |
+----+
| -1 |
| 0 |
| 2 |
+----+
Output:
+----------+
| shortest |
+----------+
| 1 |
+----------+
Explanation: The shortest distance is between points -1 and 0 which is |(-1) - 0| = 1.
Follow up: How could you optimize your solution if the Point table is ordered in ascending order?
1. 普通解法
任意两个不同的点都可以组成一对，所以我们可以让 Point 表自己 JOIN 自己。
SELECT MIN(ABS(p1.x - p2.x)) AS shortest
FROM Point p1
JOIN Point p2
ON p1.x != p2.x;
为什么这样就行？
612. Shortest Distance in a Plane
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Point2D
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
| y | int |
+-------------+------+
(x, y) is the primary key column (combination of columns with unique values) for this table.
Each row of this table indicates the position of a point on the X-Y plane.
The distance between two points p1(x1, y1) and p2(x2, y2) is sqrt((x2 - x1)2 + (y2 - y1)2).
Write a solution to report the shortest distance between any two points from the Point2D table. Round the distance to two decimal points.
The result format is in the following example.
Example 1:
Input:
Point2D table:
+----+----+
| x | y |
+----+----+
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
+----+----+
Output:
+----------+
| shortest |
+----------+
| 1.00 |
+----------+
Explanation: The shortest distance is 1.00 from point (-1, -1) to (-1, 2).
+-------------+------+
| Column Name | Type |
+-------------+------+
| x | int |
| y | int |
| z | int |
+-------------+------+
In SQL, (x, y, z) is the primary key column for this table.
Each row of this table contains the lengths of three line segments.
Report for every three line segments whether they can form a triangle.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Triangle table:
+----+----+----+
| x | y | z |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
Output:
+----+----+----+----------+
| x | y | z | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
+----+----+----+----------+
核心条件
三个边长 x, y, z 能组成三角形，当且仅当：
x + y > z
x + z > y
y + z > x
因为三条边都可以是任意大小，所以三个条件都要满足。
不过 SQL 里可以利用一个更简单的写法：最长的边必须小于另外两边之和。
SELECT
x,
y,
z,
IF(
x + y > z
AND x + z > y
AND y + z > x,
'Yes',
'No'
) AS triangle
FROM Triangle;
复制代码
优化，直接找最长边比较，
SELECT
x,
y,
z,
IF(
GREATEST(x, y, z) < x + y + z - GREATEST(x, y, z),
608. Tree Node
Medium
Topics
conpanies icon
Companies
Hint
SQL Schema
Pandas Schema
Table: Tree
+-------------+------+
| Column Name | Type |
+-------------+------+
| id | int |
| p_id | int |
+-------------+------+
id is the column with unique values for this table.
Each row of this table contains information about the id of a node and the id of its parent node in a tree.
The given structure is always a valid tree.
Each node in the tree can be one of three types:
"Leaf": if the node is a leaf node.
"Root": if the node is the root of the tree.
"Inner": If the node is neither a leaf node nor a root node.
Write a solution to report the type of each node in the tree.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
Output:
+----+-------+
| id | type |
+----+-------+
| 1 | Root |
| 2 | Inner |
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+-------+
Explanation:
Node 1 is the root node because its parent node is null and it has child nodes 2 and 3.
Node 2 is an inner node because it has parent node 1 and child node 4 and 5.
Nodes 3, 4, and 5 are leaf nodes because they have parent nodes and they do not have child nodes.
Example 2:
Input:
Tree table:
+----+------+
| id | p_id |
+----+------+
| 1 | null |
+----+------+
Output:
+----+-------+
| id | type |
+----+-------+
| 1 | Root |
+----+-------+
Explanation: If there is only one node on the tree, you only need to output its root attributes.
这题的关键是：根据 p_id 和是否存在子节点来判断类型。
1. 判断规则
对于每一个 id：
p_id IS NULL → Root
有 p_id，并且这个 id 出现在其他节点的 p_id 中 → Inner
有 p_id，但这个 id 没有出现在任何人的 p_id 中 → Leaf
所以可以用 CASE WHEN + EXISTS。
这道题真正重要的知识点，不只是 CASE WHEN，而是：
Tree 在关系型数据库里，不需要专门的 Tree 数据结构；只要记录 node → parent node 的关系，就可以表示整棵树。
# Write your MySQL query statement below
SELECT
id,
CASE
WHEN p_id IS NULL THEN 'Root'
WHEN id IN (SELECT p_id FROM Tree) THEN 'Inner'
ELSE 'Leaf'
END AS type
FROM Tree;
复制代码
为什么只需要 node + parent？
因为每个 node 只需要回答一个问题：
“我的 parent 是谁？”
例如：
2 → 1
3 → 1
4 → 2
5 → 2
把这些关系组合起来，整个 Tree 就自然出现了。
这其实就是数据库里非常重要的思想：
Tree → Parent-Child Relationship → Row
而不是：
Tree
├── Node
│ ├── Children
│ └── Children
这种程序里的对象结构。
这也解释了这道题为什么这么设计
题目给：
Tree(id, p_id)
然后让你判断：
Root
Inner
Leaf
其实是在训练你从 parent relationship 反推 tree structure。
逻辑就是：
p_id IS NULL
↓
Root
有人把我作为 p_id
↓
有 child
↓
Inner
没人把我作为 p_id
↓
没有 child
↓
Leaf
所以我会把 608 Tree Node 记成：
SQL Tree 的基本表示方法：每一行存 node_id + parent_id，整个树就被表示出来了。
1050. Actors and Directors Who Cooperated At Least Three Times
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: ActorDirector
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| actor_id | int |
| director_id | int |
| timestamp | int |
+-------------+---------+
timestamp is the primary key (column with unique values) for this table.
Write a solution to find all the pairs (actor_id, director_id) where the actor has cooperated with the director at least three times.
1045. Customers Who Bought All Products
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Customer
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
This table may contain duplicates rows.
customer_id is not NULL.
product_key is a foreign key (reference column) to Product table.
Table: Product
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key is the primary key (column with unique values) for this table.
Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.
楼主: Myron2017
狗仔卡
楼主
