刷题记录帖子

Myron2017

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;

复制代码

Myron2017

597. Friend Requests I: Overall Acceptance Rate
Easy
Topics
conpanies icon
Companies
Hint
SQL Schema
Pandas Schema
Table: FriendRequest

+----------------+---------+
| Column Name | Type |
+----------------+---------+
| sender_id    | int    |
| send_to_id    | int    |
| request_date | date |
+----------------+---------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date of the request.

Table: RequestAccepted

+----------------+---------+
| Column Name | Type |
+----------------+---------+
| requester_id | int    |
| accepter_id | int    |
| accept_date | date |
+----------------+---------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.

Find the overall acceptance rate of requests, which is the number of acceptance divided by the number of requests. Return the answer rounded to 2 decimals places.

Note that:

The accepted requests are not necessarily from the table friend_request. In this case, Count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.
It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.
If there are no requests at all, you should return 0.00 as the accept_rate.
The result format is in the following example.

Example 1:

Input:
FriendRequest table:
+-----------+------------+--------------+
| sender_id | send_to_id | request_date |
+-----------+------------+--------------+
| 1       | 2       | 2016/06/01 |
| 1       | 3       | 2016/06/01 |
| 1       | 4       | 2016/06/01 |
| 2       | 3       | 2016/06/02 |
| 3       | 4       | 2016/06/09 |
+-----------+------------+--------------+
RequestAccepted table:
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1          | 2          | 2016/06/03  |
| 1          | 3          | 2016/06/08  |
| 2          | 3          | 2016/06/08  |
| 3          | 4          | 2016/06/09  |
| 3          | 4          | 2016/06/10  |
+--------------+-------------+-------------+
Output:
+-------------+
| accept_rate |
+-------------+
| 0.8       |
+-------------+
Explanation:
There are 4 unique accepted requests, and there are 5 requests in total. So the rate is 0.80.

Follow up:

Could you find the acceptance rate for every month?
Could you find the cumulative acceptance rate for every day?

SELECT
ROUND(
IFNULL(
(SELECT COUNT(DISTINCT requester_id, accepter_id)
FROM RequestAccepted)
/
(SELECT COUNT(DISTINCT sender_id, send_to_id)
FROM FriendRequest),
0
),
2
) AS accept_rate;

复制代码

Myron2017

602. Friend Requests II: Who Has the Most Friends
Medium
Topics
conpanies icon
Companies
Hint
SQL Schema
Pandas Schema
Table: RequestAccepted

+----------------+---------+
| Column Name | Type |
+----------------+---------+
| requester_id | int    |
| accepter_id | int    |
| accept_date | date |
+----------------+---------+
(requester_id, accepter_id) is the primary key (combination of columns with unique values) for this table.
This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.

Write a solution to find the people who have the most friends and the most friends number.

The test cases are generated so that only one person has the most friends.

The result format is in the following example.

Example 1:

Input:
RequestAccepted table:
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1          | 2          | 2016/06/03  |
| 1          | 3          | 2016/06/08  |
| 2          | 3          | 2016/06/08  |
| 3          | 4          | 2016/06/09  |
+--------------+-------------+-------------+
Output:
+----+-----+
| id | num |
+----+-----+
| 3  | 3 |
+----+-----+
Explanation:
The person with id 3 is a friend of people 1, 2, and 4, so he has three friends in total, which is the most number than any others.

Follow up: In the real world, multiple people could have the same most number of friends. Could you find all these people in this case?

SELECT id, COUNT(*) AS num
FROM (
SELECT requester_id AS id
FROM RequestAccepted
UNION ALL
SELECT accepter_id AS id
FROM RequestAccepted
) t
GROUP BY id
ORDER BY num DESC
LIMIT 1;

复制代码

Myron2017

603. Consecutive Available Seats
Easy
Topics
SQL Schema
Pandas Schema
Table: Cinema

+-------------+------+
| Column Name | Type |
+-------------+------+
| seat_id    | int  |
| free       | bool |
+-------------+------+
seat_id is an auto-increment column for this table.
Each row of this table indicates whether the ith seat is free or not. 1 means free while 0 means occupied.

Find all the consecutive available seats in the cinema.

Return the result table ordered by seat_id in ascending order.

The test cases are generated so that more than two seats are consecutively available.

The result format is in the following example.

Example 1:

Input:
Cinema table:
+---------+------+
| seat_id | free |
+---------+------+
| 1    | 1 |
| 2    | 0 |
| 3    | 1 |
| 4    | 1 |
| 5    | 1 |
+---------+------+
Output:
+---------+
| seat_id |
+---------+
| 3    |
| 4    |
| 5    |
+---------+
这道 603. Consecutive Available Seats 是一个很经典的 SQL 面试题，核心考点是：

如何判断当前行的前一行 / 后一行满足某个条件。

最简单的方法是 Self Join（自己和自己 JOIN）。
路

我们把 Cinema 看成两张表：

Cinema c1       Cinema c2
-----------       -----------
seat_id = 3       seat_id = 4
free = 1          free = 1

JOIN 条件：

ABS(c1.seat_id - c2.seat_id) = 1

意思就是：

两个 seat_id 相差 1。

然后要求：

c1.free = 1
AND c2.free = 1

也就是：

当前座位和相邻座位都是空闲的。

SELECT DISTINCT c1.seat_id
FROM Cinema c1
JOIN Cinema c2
ON ABS(c1.seat_id - c2.seat_id) = 1
WHERE c1.free = 1
AND c2.free = 1
ORDER BY c1.seat_id;

复制代码

Myron2017

601. Human Traffic of Stadium
Hard
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Stadium

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id          | int    |
| visit_date | date |
| people       | int    |
+---------------+---------+
visit_date is the column with unique values for this table.
Each row of this table contains the visit date and visit id to the stadium with the number of people during the visit.
As the id increases, the date increases as well.

Write a solution to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.

Return the result table ordered by visit_date in ascending order.

The result format is in the following example.

Example 1:

Input:
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10       |
| 2 | 2017-01-02 | 109    |
| 3 | 2017-01-03 | 150    |
| 4 | 2017-01-04 | 99       |
| 5 | 2017-01-05 | 145    |
| 6 | 2017-01-06 | 1455    |
| 7 | 2017-01-07 | 199    |
| 8 | 2017-01-09 | 188    |
+------+------------+-----------+
Output:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145    |
| 6 | 2017-01-06 | 1455    |
| 7 | 2017-01-07 | 199    |
| 8 | 2017-01-09 | 188    |
+------+------------+-----------+
Explanation:
The four rows with ids 5, 6, 7, and 8 have consecutive ids and each of them has >= 100 people attended. Note that row 8 was included even though the visit_date was not the next day after row 7.
The rows with ids 2 and 3 are not included because we need at least three consecutive ids.

这道 601. Human Traffic of Stadium 是一道非常经典的 Hard SQL。

它和你刚刚做的 603 有直接关系：都是找 consecutive rows，但 601 更难，因为：

不是找“相邻的一对”，而是找出属于连续 ≥ 3 个满足条件的区间里的所有行。

1. 先抓住最重要的条件

题目要求每一行满足：

people >= 100

然后这些行还必须有：

连续的 id

并且连续长度至少是：

3

注意：

连续的是 id，不是 visit_date。

例如：

id=7 → 2017-01-07
id=8 → 2017-01-09

虽然日期跳了一天，但：

7, 8

仍然是连续 id。

2. 最容易想到但不完整的方法

首先把 people < 100 去掉：

SELECT *
FROM Stadium
WHERE people >= 100;

例子变成：

id people
2 109
3 150
5 145
6 1455
7 199
8 188

现在需要找到：

2,3    ❌ 只有2个
5,6,7,8 ✅ 有4个

所以最终返回：

5
6
7
8

用 LAG / LEAD：我更推荐这个

SELECT id, visit_date, people
FROM (
SELECT
*,
LAG(id, 1) OVER (ORDER BY id) AS prev_id,
LAG(id, 2) OVER (ORDER BY id) AS prev2_id,
LEAD(id, 1) OVER (ORDER BY id) AS next_id,
LEAD(id, 2) OVER (ORDER BY id) AS next2_id
FROM Stadium
WHERE people >= 100
) t
WHERE
(prev_id = id - 1 AND prev2_id = id - 2)
OR (prev_id = id - 1 AND next_id = id + 1)
OR (next_id = id + 1 AND next2_id = id + 2)
ORDER BY visit_date;

复制代码

为什么需要三个条件？

我们现在只保留：

people >= 100

假设得到：

id
--
2
3
5
6
7
8

对于 5：

prev2 = 3
prev  = 2
id = 5

不连续。

但是：

5 → 6 → 7

所以 5 应该被保留。

于是我们需要检查三种情况。

情况 1：当前行是连续区间的第三个或之后
id-2, id-1, id

对应：

prev2_id = id - 2
AND prev_id = id - 1

例如：

5, 6, 7
   ↑
   当前

7 可以被找到。

情况 2：当前行在连续区间中间
id-1, id, id+1

对应：

prev_id = id - 1
AND next_id = id + 1

例如：

5, 6, 7
↑
  当前

6 可以被找到。

情况 3：当前行是连续区间的第一个或之前
id, id+1, id+2

对应：

next_id = id + 1
AND next2_id = id + 2

例如：

5, 6, 7
↑
当前

5 可以被找到。

所以：

WHERE
   (prev_id = id - 1 AND prev2_id = id - 2)
OR (prev_id = id - 1 AND next_id = id + 1)
OR (next_id = id + 1 AND next2_id = id + 2)

本质就是：

当前行前后至少能找到两个连续的、高客流记录。

Myron2017

626. Exchange Seats
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Seat

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id       | int    |
| student    | varchar |
+-------------+---------+
id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
The ID sequence always starts from 1 and increments continuously.

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

The result format is in the following example.

Example 1:

Input:
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot |
| 2  | Doris |
| 3  | Emerson |
| 4  | Green |
| 5  | Jeames  |
+----+---------+
Output:
+----+---------+
| id | student |
+----+---------+
| 1  | Doris |
| 2  | Abbot |
| 3  | Green |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
Explanation:
Note that if the number of students is odd, there is no need to change the last one's seat.

奇数 id → 下一位；偶数 id → 上一位。

但最后一个奇数 id 如果没有下一位，就保持不变。

SELECT
CASE
WHEN id % 2 = 1 AND id < (SELECT MAX(id) FROM Seat)
THEN id + 1
WHEN id % 2 = 0
THEN id - 1
ELSE id
END AS id,
student
FROM Seat
ORDER BY id;

复制代码

Myron2017

619. Biggest Single Number
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: MyNumbers

+-------------+------+
| Column Name | Type |
+-------------+------+
| num | int |
+-------------+------+
This table may contain duplicates (In other words, there is no primary key for this table in SQL).
Each row of this table contains an integer.

A single number is a number that appeared only once in the MyNumbers table.

Find the largest single number. If there is no single number, report null.

The result format is in the following example.

Example 1:

Input:
MyNumbers table:
+-----+
| num |
+-----+
| 8 |
| 8 |
| 3 |
| 3 |
| 1 |
| 4 |
| 5 |
| 6 |
+-----+
Output:
+-----+
| num |
+-----+
| 6 |
+-----+
Explanation: The single numbers are 1, 4, 5, and 6.
Since 6 is the largest single number, we return it.
Example 2:

Input:
MyNumbers table:
+-----+
| num |
+-----+
| 8 |
| 8 |
| 7 |
| 7 |
| 3 |
| 3 |
| 3 |
+-----+
Output:
+------+
| num |
+------+
| null |
+------+
Explanation: There are no single numbers in the input table so we return null.

一步一步理解
第一步：找 single number
SELECT num
FROM MyNumbers
GROUP BY num
HAVING COUNT(*) = 1;

例子得到：

1
4
5
6

这里：

GROUP BY num
HAVING COUNT(*) = 1

就是：

每个数字分组，只保留出现次数为 1 的数字。

第二步：找最大的

把上面的结果当成临时表：

1
4
5
6

然后：

SELECT MAX(num)

得到：

6
为什么没有 single number 时会返回 NULL？

这也是这道题很值得注意的地方。

如果：

8 → 2次
7 → 2次
3 → 3次

那么：

HAVING COUNT(*) = 1

一个结果都没有。

于是：

SELECT MAX(num)
FROM (...)

对一个空集合求 MAX：

NULL

恰好就是题目要求的结果。

所以这里不需要：

IFNULL(...)

也不需要自己处理 NULL。

SELECT MAX(num) AS num
FROM (
SELECT num
FROM MyNumbers
GROUP BY num
HAVING COUNT(*) = 1
) t;

复制代码

Myron2017

SQL Pivot（行转列）

核心难点不是 GROUP BY，而是：

每个 continent 内部按 name 排序，然后把第 1 个、第 2 个……学生分别放到对应列的第 1 行、第 2 行……

1. 先看数据

原始数据：

Jane America
Pascal  Europe
Xi    Asia
Jack America

先分别按名字排序：

America: Jack, Jane
Asia: Xi
Europe:  Pascal

然后按照“第几个学生”对齐：

America  Asia  Europe
Jack    Xi Pascal
Jane    NULL  NULL

所以我们需要给每个 continent 内的学生一个排名编号：

Jack America 1
Jane America 2
Xi    Asia    1
Pascal  Europe 1

这里最重要的工具就是：

ROW_NUMBER() OVER (
PARTITION BY continent
ORDER BY name
)
2. 第一步：给每个洲编号
SELECT
name,
continent,
ROW_NUMBER() OVER (
      PARTITION BY continent
      ORDER BY name
) AS rn
FROM Student;

得到：

name continent rn
----------------------
Jack America    1
Jane America    2
Xi    Asia       1
Pascal  Europe    1

这里一定要理解：

PARTITION BY continent

相当于：

America 自己编号，Asia 自己编号，Europe 自己编号。

所以：

America: Jack 1, Jane 2
Asia: Xi 1
Europe:  Pascal 1

而不是所有学生一起编号。

3. 第二步：把 continent 变成列

我们希望：

America | Asia | Europe

这就是 Pivot。

SQL 里通常使用：

MAX(CASE WHEN ... THEN ... END)

来实现。

完整答案：

SELECT
MAX(CASE WHEN continent = 'America' THEN name END) AS America,
MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
FROM (
SELECT
      name,
      continent,
      ROW_NUMBER() OVER (
         PARTITION BY continent
         ORDER BY name
      ) AS rn
FROM Student
) t
GROUP BY rn
ORDER BY rn;
4. 为什么 GROUP BY rn？

这是这道题最关键的地方。

子查询产生：

name continent rn
----------------------
Jack America    1
Jane America    2
Xi    Asia       1
Pascal  Europe    1

我们按照 rn 分组：

rn = 1
America → Jack
Asia → Xi
Europe  → Pascal

所以：

Jack | Xi | Pascal
rn = 2
America → Jane
Asia → 没有
Europe  → 没有

所以：

Jane | NULL | NULL

最终：

America | Asia | Europe
-------- | ---- | ------
Jack | Xi | Pascal
Jane | NULL | NULL
5. 为什么这里可以用 MAX()？

你可能会问：

每个 rn + continent 实际只有一个 name，为什么还要 MAX()？

因为 SQL 需要一个聚合函数来把多行压缩成一行。

例如：

MAX(CASE WHEN continent = 'America' THEN name END)

对于 rn = 1：

Jack
NULL
NULL

MAX() 最后得到：

Jack

对于 rn = 2：

Jane
NULL
NULL

得到：

Jane

所以这里的 MAX() 不是为了找最大的名字，而是一个 Pivot 技巧：

利用 CASE WHEN 把目标值放出来，再用 MAX() 把它从多行压成一个单元格。

这个模式非常值得记住：

MAX(CASE WHEN category = 'A' THEN value END)

就是 SQL 中非常常见的条件聚合 / 手工 Pivot。

6. 为什么题目特别告诉我们 America 学生最多？

题目说：

student number from America is not less than either Asia or Europe.

也就是：

America >= Asia
America >= Europe

因此我们可以：

GROUP BY rn

然后直接输出。

因为 America 的人数最多，所以 rn 一定可以覆盖其他 continent 的所有行。

例如：

America:  A B C D
Asia:    X Y
Europe: P Q Q

排名：

rn=1 A X P
rn=2 B Y Q
rn=3 C NULL Q
rn=4 D NULL NULL

刚好形成最终结果。

7. 这道题真正应该记住的模板

这道题可以拆成两个经典 SQL 技巧：

技巧一：组内排名
ROW_NUMBER() OVER (
PARTITION BY continent
ORDER BY name
)

含义：

每个 continent 内按照 name 排名。

技巧二：条件聚合实现 Pivot
MAX(CASE
WHEN continent = 'America'
THEN name
END) AS America

三个 continent 就写三个：

MAX(CASE WHEN continent = 'America' THEN name END) AS America,
MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
Follow-up：不知道哪个 continent 人最多怎么办？

这里就比较有意思了。

原题因为告诉我们：

America >= Asia
America >= Europe

所以可以放心地用 America 的排名作为最终行数。

但是如果不知道哪个洲人数最多，就不能依赖 America。

实际上，前面的 ROW_NUMBER() 完全不需要修改：

SELECT
name,
continent,
ROW_NUMBER() OVER (
      PARTITION BY continent
      ORDER BY name
) AS rn
FROM Student

然后还是：

GROUP BY rn

即可。

也就是说，核心 SQL 仍然是：

SELECT
MAX(CASE WHEN continent = 'America' THEN name END) AS America,
MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
FROM (
SELECT
      name,
      continent,
      ROW_NUMBER() OVER (
         PARTITION BY continent
         ORDER BY name
      ) AS rn
FROM Student
) t
GROUP BY rn
ORDER BY rn;

因为 GROUP BY rn 会自动产生：

rn = 1
rn = 2
rn = 3
...

直到最大的 rn。

所以即使：

America: 2
Asia: 5
Europe:  3

也会产生 5 行：

America | Asia | Europe
-------- | ---- | ------
...    | ...  | ...
...    | ...  | ...
NULL    | ...  | ...
NULL    | ...  | ...
NULL    | ...  | ...

所以这个 Follow-up 的关键结论是：其实不需要知道谁人数最多。 GROUP BY rn 会自然决定最终需要多少行。

SELECT
MAX(CASE WHEN continent = 'America' THEN name END) AS America,
MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
FROM (
SELECT
name,
continent,
ROW_NUMBER() OVER (
PARTITION BY continent
ORDER BY name
) AS rn
FROM Student
) t
GROUP BY rn
ORDER BY rn;

复制代码

完全可以把这道题理解成 GROUP BY rn + MAX(CASE WHEN...) 的配合。

整个逻辑其实就三步：

① ROW_NUMBER() 先给每个 continent 内的学生编号
ROW_NUMBER() OVER (
PARTITION BY continent
ORDER BY name
) AS rn

得到：

Jack America 1
Jane America 2
Xi    Asia    1
Pascal  Europe 1
② GROUP BY rn 决定最终有多少行
GROUP BY rn

于是：

rn = 1 → 第一行
rn = 2 → 第二行
③ MAX(CASE WHEN...) 把不同 continent 塞进不同列

例如：

MAX(CASE WHEN continent = 'America' THEN name END) AS America

对于 rn = 1：

Jack
NULL
NULL

MAX() 把它压成：

Jack

Asia 和 Europe 同理。

所以可以把整个算法记成：

ROW_NUMBER()
   ↓
给每个组里的数据编号
   ↓
GROUP BY rn
   ↓
每一个 rn 变成一行
   ↓
MAX(CASE WHEN ...)
   ↓
把不同类别变成不同列
最核心的一句话

ROW_NUMBER() 负责“对齐”，GROUP BY rn 负责“成行”，MAX(CASE WHEN) 负责“成列”。

这就是这道题最值得记住的模式。

Myron2017

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.

这道题的核心其实就两步：

算每个月公司的平均工资
算每个月每个部门的平均工资，然后比较

最容易踩的坑是：公司的平均工资必须按“员工工资记录”直接计算，不能先算部门平均再对部门平均求平均。因为每个部门的员工人数可能不同。

WITH monthly_company AS (
SELECT
DATE_FORMAT(pay_date, '%Y-%m') AS pay_month,
AVG(amount) AS company_avg
FROM Salary
GROUP BY DATE_FORMAT(pay_date, '%Y-%m')
),
monthly_department AS (
SELECT
DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month,
e.department_id,
AVG(s.amount) AS department_avg
FROM Salary s
JOIN Employee e
ON s.employee_id = e.employee_id
GROUP BY
DATE_FORMAT(s.pay_date, '%Y-%m'),
e.department_id
)
SELECT
d.pay_month,
d.department_id,
CASE
WHEN d.department_avg > c.company_avg THEN 'higher'
WHEN d.department_avg < c.company_avg THEN 'lower'
ELSE 'same'
END AS comparison
FROM monthly_department d
JOIN monthly_company c
ON d.pay_month = c.pay_month;

复制代码

一步一步理解
1. 公司每个月平均工资
SELECT
DATE_FORMAT(pay_date, '%Y-%m') AS pay_month,
AVG(amount) AS company_avg
FROM Salary
GROUP BY DATE_FORMAT(pay_date, '%Y-%m')

得到类似：

2017-02 7000
2017-03 8333.33

因为公司平均工资只和月份有关，所以只 GROUP BY pay_month。

2. 每个月、每个部门平均工资

需要把 Salary 和 Employee 连接起来，因为 department_id 在 Employee 表：

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

就是：

部门平均 > 公司平均 → higher
部门平均 < 公司平均 → lower
部门平均 = 公司平均 → same
一个非常重要的错误写法

很多人会想到：

AVG(department_avg)

来计算公司的平均工资。

这是错的。

例如：

Department A: 1 employee, salary = 10,000
Department B: 9 employees, salary = 1,000

真实公司平均：

(10000 + 9 * 1000) / 10 = 1900

但如果对部门平均求平均：

(10000 + 1000) / 2 = 5500

完全不一样。

所以这题要记住：

Company AVG → 直接对 Salary.amount 求 AVG
Department AVG → JOIN Employee 后按 month + department 求 AVG

Myron2017

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.

核心就是准确理解 Follow 两列的含义：

followee  ← 被关注的人
follower  ← 关注别人的人

一个用户要成为 second-degree follower，必须同时满足：

他出现在 follower 列 → 他至少关注了一个人
他出现在 followee 列 → 至少有人关注他

而我们最后要统计的是：有多少人关注这个用户，所以直接对 followee 分组即可。

SELECT
followee AS follower,
COUNT(*) AS num
FROM Follow
WHERE followee IN (
SELECT follower
FROM Follow
)
GROUP BY followee
ORDER BY followee;

复制代码

刷题记录帖子

浏览过的版块