刷题记录帖子

1294. Weather Type in Each Country
Easy
Topics
SQL Schema
Pandas Schema
Table: Countries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country_id    | int     |
| country_name  | varchar |
+---------------+---------+
country_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one country.


Table: Weather

+---------------+------+
| Column Name   | Type |
+---------------+------+
| country_id    | int  |
| weather_state | int  |
| day           | date |
+---------------+------+
(country_id, day) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the weather state in a country for one day.


Write a solution to find the type of weather in each country for November 2019.

The type of weather is:

Cold if the average weather_state is less than or equal 15,
Hot if the average weather_state is greater than or equal to 25, and
Warm otherwise.
Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Countries table:
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 2          | USA          |
| 3          | Australia    |
| 7          | Peru         |
| 5          | China        |
| 8          | Morocco      |
| 9          | Spain        |
+------------+--------------+
Weather table:
+------------+---------------+------------+
| country_id | weather_state | day        |
+------------+---------------+------------+
| 2          | 15            | 2019-11-01 |
| 2          | 12            | 2019-10-28 |
| 2          | 12            | 2019-10-27 |
| 3          | -2            | 2019-11-10 |
| 3          | 0             | 2019-11-11 |
| 3          | 3             | 2019-11-12 |
| 5          | 16            | 2019-11-07 |
| 5          | 18            | 2019-11-09 |
| 5          | 21            | 2019-11-23 |
| 7          | 25            | 2019-11-28 |
| 7          | 22            | 2019-12-01 |
| 7          | 20            | 2019-12-02 |
| 8          | 25            | 2019-11-05 |
| 8          | 27            | 2019-11-15 |
| 8          | 31            | 2019-11-25 |
| 9          | 7             | 2019-10-23 |
| 9          | 3             | 2019-12-23 |
+------------+---------------+------------+
Output:
+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA          | Cold         |
| Australia    | Cold         |
| Peru         | Hot          |
| Morocco      | Hot          |
| China        | Warm         |
+--------------+--------------+
Explanation:
Average weather_state in USA in November is (15) / 1 = 15 so weather type is Cold.
Average weather_state in Austraila in November is (-2 + 0 + 3) / 3 = 0.333 so weather type is Cold.
Average weather_state in Peru in November is (25) / 1 = 25 so the weather type is Hot.
Average weather_state in China in November is (16 + 18 + 21) / 3 = 18.333 so weather type is Warm.
Average weather_state in Morocco in November is (25 + 27 + 31) / 3 = 27.667 so weather type is Hot.
We know nothing about the average weather_state in Spain in November so we do not include it in the result table.

思路

按国家统计 2019 年 11 月的平均 weather_state，再根据平均值判断天气类型：

平均值 ≤ 15：Cold
平均值 ≥ 25：Hot
其余：Warm

有三个细节：

先过滤日期，再分组求平均。只有 11 月的记录参与计算，10 月和 12 月的不算。
11 月没有任何记录的国家不输出。如西班牙只有 10 月和 12 月的数据，题目要求不出现在结果里，所以用 INNER JOIN，不需要 LEFT JOIN。
用精确的平均值比较，不要先取整。平均值是小数，如 15.5 既大于 15 又小于 25，应判为 Warm。如果先四舍五入成 16 或 15，边界就会判错。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     c.country_name,
   
4.     CASE
   
5.         WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
   
6.         WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
   
7.         ELSE 'Warm'
   
8.     END AS weather_type
   
9. FROM Countries c
   
10. JOIN Weather w
    
11.     ON c.country_id = w.country_id
    
12. WHERE w.day >= '2019-11-01'
    
13.   AND w.day <  '2019-12-01'
    
14. GROUP BY c.country_id, c.country_name;

复制代码

逐步解释
JOIN：Weather 里只有 country_id，要连接 Countries 才能拿到国家名。用 INNER JOIN，这样 11 月没有数据的国家自然被排除。
WHERE 过滤日期：用半开区间"大于等于 11 月 1 日、小于 12 月 1 日"，不用自己判断 11 月有几天，即使 day 带有时间部分也不会漏掉 11 月 30 日。
GROUP BY：按国家分组，对每个国家 11 月的所有记录求平均。
CASE WHEN：条件按顺序判断，先看是否 ≤ 15，再看是否 ≥ 25，都不满足就是 Warm。两个边界都是闭区间，所以平均值恰好等于 15 是 Cold，恰好等于 25 是 Hot。
可以用别名简化：把 AVG(w.weather_state) 写三遍有点重复，也可以先在子查询里算好平均值再判断，结果相同。

1285. Find the Start and End Number of Continuous Ranges
Medium
Topics
SQL Schema
Pandas Schema
Table: Logs

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
log_id is the column of unique values for this table.
Each row of this table contains the ID in a log Table.


Write a solution to find the start and end number of continuous ranges in the table Logs.

Return the result table ordered by start_id.

The result format is in the following example.



Example 1:

Input:
Logs table:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+
Output:
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
Explanation:
The result table should contain all ranges in table Logs.
From 1 to 3 is contained in the table.
From 4 to 6 is missing in the table
From 7 to 8 is contained in the table.
Number 9 is missing from the table.
Number 10 is contained in the table.


思路

这是经典的**连续区间（gaps and islands）**问题。目标是把连续的 log_id 合并成一段，输出每段的起点和终点。

核心技巧是值减去行号：

把 log_id 从小到大排序，编号 rn = 1, 2, 3, ...。
在一段连续的数里，log_id 每增加 1，rn 也增加 1，所以 log_id - rn 保持不变。
一旦出现间断（log_id 跳过了某些数），log_id 多涨了，而 rn 只涨 1，差值就变大。

所以差值相同的行属于同一个连续区间，每组取最小值和最大值即可。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     MIN(log_id) AS start_id,
   
4.     MAX(log_id) AS end_id
   
5. FROM (
   
6.     SELECT
   
7.         log_id,
   
8.         log_id - ROW_NUMBER() OVER (ORDER BY log_id) AS grp
   
9.     FROM Logs
   
10. ) t
    
11. GROUP BY grp
    
12. ORDER BY start_id;

复制代码

逐步解释
内层子查询：用 ROW_NUMBER() OVER (ORDER BY log_id) 按 log_id 排序编号，再用 log_id 减去行号得到分组标识 grp。
外层 GROUP BY grp：同一个 grp 就是同一段连续的数，MIN 是起点，MAX 是终点。
用 ROW_NUMBER 是安全的：log_id 是唯一值，不会有重复，所以行号和排序位置一一对应。
排序：按 start_id 升序输出。


SQL 解法二：找起点和终点（不依赖窗口函数）

1. # Write your MySQL query statement below
   
2. SELECT s.start_id, MIN(e.end_id) AS end_id
   
3. FROM (
   
4.     SELECT log_id AS start_id
   
5.     FROM Logs
   
6.     WHERE log_id - 1 NOT IN (SELECT log_id FROM Logs)
   
7. ) s
   
8. JOIN (
   
9.     SELECT log_id AS end_id
   
10.     FROM Logs
    
11.     WHERE log_id + 1 NOT IN (SELECT log_id FROM Logs)
    
12. ) e
    
13.     ON s.start_id <= e.end_id
    
14. GROUP BY s.start_id
    
15. ORDER BY s.start_id;

复制代码

起点：log_id - 1 不在表里，说明前一个数缺失，这个数是某段的开头。
终点：log_id + 1 不在表里，说明后一个数缺失，这个数是某段的结尾。
配对：每个起点对应的终点，是不小于它的最小终点，所以用 s.start_id <= e.end_id 连接，再取 MIN(e.end_id)。
NOT IN 是安全的：log_id 是唯一值且不会为 NULL。

1369. Get the Second Most Recent Activity
Hard
Topics
SQL Schema
Pandas Schema
Table: UserActivity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
This table may contain duplicates rows.
This table contains information about the activity performed by each user in a period of time.
A person with username performed an activity from startDate to endDate.


Write a solution to show the second most recent activity of each user.

If the user only has one activity, return that one. A user cannot perform more than one activity at the same time.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
UserActivity table:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Travel       | 2020-02-12  | 2020-02-20  |
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Alice      | Travel       | 2020-02-24  | 2020-02-28  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+
Output:
+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+
Explanation:
The most recent activity of Alice is Travel from 2020-02-24 to 2020-02-28, before that she was dancing from 2020-02-21 to 2020-02-23.
Bob only has one record, we just take that one.


思路

每个用户要取开始时间第二晚的活动。如果用户只有一条活动，就返回那一条。

按 username 分区，在每个分区内按 startDate 降序排名，最近的是第 1，第二近的是第 2。
同时数出每个用户的活动总数 cnt。
保留 排名 = 2 的行，或者 cnt = 1 的行（只有一条活动的用户）。

题目保证一个用户不会同时做两件事，所以同一用户的 startDate 不会重复，排序没有歧义。

1. SELECT username, activity, startDate, endDate
   
2. FROM (
   
3.     SELECT
   
4.         username,
   
5.         activity,
   
6.         startDate,
   
7.         endDate,
   
8.         ROW_NUMBER() OVER (PARTITION BY username ORDER BY startDate DESC) AS rn,
   
9.         COUNT(*)     OVER (PARTITION BY username)                         AS cnt
   
10.     FROM UserActivity
    
11. ) t
    
12. WHERE rn = 2 OR cnt = 1;
    

复制代码

逐步解释
ROW_NUMBER() OVER (PARTITION BY username ORDER BY startDate DESC)：每个用户内部按开始时间从晚到早编号，1 是最近的活动，2 是第二近的。
COUNT(*) OVER (PARTITION BY username)：把每个用户的活动总数附在他的每一行上，窗口里不写 ORDER BY，范围就是整个分区。
WHERE rn = 2 OR cnt = 1：
活动不止一条的用户，取第 2 条。
只有一条活动的用户，没有"第 2 条"，所以靠 cnt = 1 把唯一的那条保留下来。
为什么要套一层子查询：WHERE 里不能直接引用窗口函数的结果，要先在子查询里算好 rn 和 cnt，外层再过滤。
为什么用 ROW_NUMBER 而不是 RANK：表可能有重复行。如果某用户只有一条活动但重复了两次，ROW_NUMBER 仍能给出 rn = 2 的那一行，RANK 会让两行并列第 1，导致这个用户消失。

1. # Write your MySQL query statement below
   
2. SELECT a.username, a.activity, a.startDate, a.endDate
   
3. FROM UserActivity a
   
4. WHERE (
   
5.         SELECT COUNT(*)
   
6.         FROM UserActivity b
   
7.         WHERE b.username = a.username
   
8.           AND b.startDate > a.startDate
   
9.       ) = 1
   
10.    OR (
    
11.         SELECT COUNT(*)
    
12.         FROM UserActivity b
    
13.         WHERE b.username = a.username
    
14.       ) = 1;

复制代码

第一个条件：数出同一用户中开始时间比当前行更晚的活动数。如果恰好是 1，说明当前行是第二近的。
第二个条件：该用户的活动总数为 1，说明这是唯一的一条。
这种写法在 MySQL 5.7 等旧版本里也能用，但每行都要执行子查询，数据量大时比窗口函数慢。

1364. Number of Trusted Contacts of a Customer
Medium
Topics
SQL Schema
Pandas Schema
Table: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| customer_name | varchar |
| email         | varchar |
+---------------+---------+
customer_id is the column of unique values for this table.
Each row of this table contains the name and the email of a customer of an online shop.


Table: Contacts

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | id      |
| contact_name  | varchar |
| contact_email | varchar |
+---------------+---------+
(user_id, contact_email) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the name and email of one contact of customer with user_id.
This table contains information about people each customer trust. The contact may or may not exist in the Customers table.


Table: Invoices

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| invoice_id   | int     |
| price        | int     |
| user_id      | int     |
+--------------+---------+
invoice_id is the column of unique values for this table.
Each row of this table indicates that user_id has an invoice with invoice_id and a price.


Write a solution to find the following for each invoice_id:

customer_name: The name of the customer the invoice is related to.
price: The price of the invoice.
contacts_cnt: The number of contacts related to the customer.
trusted_contacts_cnt: The number of contacts related to the customer and at the same time they are customers to the shop. (i.e their email exists in the Customers table.)
Return the result table ordered by invoice_id.

The result format is in the following example.



Example 1:

Input:
Customers table:
+-------------+---------------+--------------------+
| customer_id | customer_name | email              |
+-------------+---------------+--------------------+
| 1           | Alice         | alice@leetcode.com |
| 2           | Bob           | bob@leetcode.com   |
| 13          | John          | john@leetcode.com  |
| 6           | Alex          | alex@leetcode.com  |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user_id     | contact_name | contact_email      |
+-------------+--------------+--------------------+
| 1           | Bob          | bob@leetcode.com   |
| 1           | John         | john@leetcode.com  |
| 1           | Jal          | jal@leetcode.com   |
| 2           | Omar         | omar@leetcode.com  |
| 2           | Meir         | meir@leetcode.com  |
| 6           | Alice        | alice@leetcode.com |
+-------------+--------------+--------------------+
Invoices table:
+------------+-------+---------+
| invoice_id | price | user_id |
+------------+-------+---------+
| 77         | 100   | 1       |
| 88         | 200   | 1       |
| 99         | 300   | 2       |
| 66         | 400   | 2       |
| 55         | 500   | 13      |
| 44         | 60    | 6       |
+------------+-------+---------+
Output:
+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44         | Alex          | 60    | 1            | 1                    |
| 55         | John          | 500   | 0            | 0                    |
| 66         | Bob           | 400   | 2            | 0                    |
| 77         | Alice         | 100   | 3            | 2                    |
| 88         | Alice         | 200   | 3            | 2                    |
| 99         | Bob           | 300   | 2            | 0                    |
+------------+---------------+-------+--------------+----------------------+
Explanation:
Alice has three contacts, two of them are trusted contacts (Bob and John).
Bob has two contacts, none of them is a trusted contact.
Alex has one contact and it is a trusted contact (Alice).
John doesn't have any contacts.


思路

每张发票要输出一行，并带上两个统计值：

contacts_cnt：这位顾客一共有多少个联系人。
trusted_contacts_cnt：这些联系人中有多少人的邮箱也在 Customers 表里，也就是他们本身也是店里的顾客。

难点有两个：

没有联系人的顾客（如 John）也要输出，两个计数都是 0，所以对 Contacts 必须用 LEFT JOIN。
判断联系人是不是顾客，要拿 contact_email 去匹配 Customers.email，不是按 user_id。联系人的邮箱在 Customers 里存在，就算受信任联系人。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     i.invoice_id,
   
4.     c.customer_name,
   
5.     i.price,
   
6.     COUNT(ct.contact_email)  AS contacts_cnt,
   
7.     COUNT(c2.customer_id)    AS trusted_contacts_cnt
   
8. FROM Invoices i
   
9. JOIN Customers c
   
10.     ON i.user_id = c.customer_id
    
11. LEFT JOIN Contacts ct
    
12.     ON ct.user_id = c.customer_id
    
13. LEFT JOIN Customers c2
    
14.     ON ct.contact_email = c2.email
    
15. GROUP BY i.invoice_id, c.customer_name, i.price
    
16. ORDER BY i.invoice_id;

复制代码

逐步解释
JOIN Customers c：通过发票的 user_id 找到顾客，拿到 customer_name。
LEFT JOIN Contacts ct：把这位顾客的所有联系人连上来。顾客没有联系人时，ct 的列全是 NULL，但这一行仍然保留。
LEFT JOIN Customers c2：再用联系人的邮箱去 Customers 里找。找到了，说明这个联系人本身也是顾客，c2.customer_id 非空。找不到就是 NULL。
COUNT(ct.contact_email)：COUNT(列) 只统计非 NULL 的值，所以没有联系人的顾客结果是 0。这里不能写 COUNT(*)，因为没有联系人时那一行仍然存在，会被错误地数成 1。
COUNT(c2.customer_id)：同理，只数匹配上的联系人，没有匹配的结果是 0。
按发票分组：同一位顾客有多张发票时（如 Alice 有 77 和 88），每张发票都会和她的全部联系人相乘一遍。但因为按 invoice_id 分组，每张发票各自一组，计数互不影响，所以两张发票都得到 3 和 2。
排序：按 invoice_id 升序。
一个小前提

c2 这次连接假设 Customers.email 没有重复。题目只保证 customer_id 唯一，没有说 email 唯一。如果邮箱可能重复，一个联系人会匹配到多行，计数会偏大。想稳妥的话，可以把"是否是顾客"改成 EXISTS 判断：

sql
SELECT
    i.invoice_id,
    c.customer_name,
    i.price,
    COUNT(ct.contact_email) AS contacts_cnt,
    SUM(CASE WHEN EXISTS (
            SELECT 1 FROM Customers x WHERE x.email = ct.contact_email
        ) THEN 1 ELSE 0 END) AS trusted_contacts_cnt
FROM Invoices i
JOIN Customers c ON i.user_id = c.customer_id
LEFT JOIN Contacts ct ON ct.user_id = c.customer_id
GROUP BY i.invoice_id, c.customer_name, i.price
ORDER BY i.invoice_id;

EXISTS 只关心"有没有"，不管匹配到几行，所以不会重复计数。当联系人为 NULL（顾客没有联系人）时，EXISTS 为假，CASE 得 0，SUM 结果是 0。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     i.invoice_id,
   
4.     c.customer_name,
   
5.     i.price,
   
6.     COUNT(ct.contact_email) AS contacts_cnt,
   
7.     SUM(CASE WHEN EXISTS (
   
8.             SELECT 1 FROM Customers x WHERE x.email = ct.contact_email
   
9.         ) THEN 1 ELSE 0 END) AS trusted_contacts_cnt
   
10. FROM Invoices i
    
11. JOIN Customers c ON i.user_id = c.customer_id
    
12. LEFT JOIN Contacts ct ON ct.user_id = c.customer_id
    
13. GROUP BY i.invoice_id, c.customer_name, i.price
    
14. ORDER BY i.invoice_id;

复制代码

1355. Activity Participants
Medium
Topics
SQL Schema
Pandas Schema
Table: Friends

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| activity      | varchar |
+---------------+---------+
id is the id of the friend and the primary key for this table in SQL.
name is the name of the friend.
activity is the name of the activity which the friend takes part in.


Table: Activities

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
In SQL, id is the primary key for this table.
name is the name of the activity.


Find the names of all the activities with neither the maximum nor the minimum number of participants.

Each activity in the Activities table is performed by any person in the table Friends.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Friends table:
+------+--------------+---------------+
| id   | name         | activity      |
+------+--------------+---------------+
| 1    | Jonathan D.  | Eating        |
| 2    | Jade W.      | Singing       |
| 3    | Victor J.    | Singing       |
| 4    | Elvis Q.     | Eating        |
| 5    | Daniel A.    | Eating        |
| 6    | Bob B.       | Horse Riding  |
+------+--------------+---------------+
Activities table:
+------+--------------+
| id   | name         |
+------+--------------+
| 1    | Eating       |
| 2    | Singing      |
| 3    | Horse Riding |
+------+--------------+
Output:
+--------------+
| activity     |
+--------------+
| Singing      |
+--------------+
Explanation:
Eating activity is performed by 3 friends, maximum number of participants, (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding activity is performed by 1 friend, minimum number of participants, (Bob B.)
Singing is performed by 2 friends (Victor J. and Jade W.)

思路

要找的是参与人数既不是最多、也不是最少的活动。分两步：

按 activity 分组，数出每个活动的参与人数。
找出人数的最小值和最大值，只保留人数严格大于最小值且严格小于最大值的活动。

两个细节：

并列的情况：如果有多个活动并列最多（或并列最少），它们都应该被排除。用"严格大于最小值、严格小于最大值"判断，能自然处理并列，不需要排名函数。
不需要连接 Activities 表：题目保证 Activities 里的每个活动都至少有一个人参加，所以直接统计 Friends 表就能覆盖全部活动。

1. # Write your MySQL query statement below
   
2. WITH cnt AS (
   
3.     SELECT activity, COUNT(*) AS c
   
4.     FROM Friends
   
5.     GROUP BY activity
   
6. )
   
7. SELECT activity
   
8. FROM cnt
   
9. WHERE c > (SELECT MIN(c) FROM cnt)
   
10.   AND c < (SELECT MAX(c) FROM cnt);

复制代码

1350. Students With Invalid Departments
Easy
Topics
SQL Schema
Pandas Schema
Table: Departments

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
In SQL, id is the primary key of this table.
The table has information about the id of each department of a university.


Table: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| department_id | int     |
+---------------+---------+
In SQL, id is the primary key of this table.
The table has information about the id of each student at a university and the id of the department he/she studies at.


Find the id and the name of all students who are enrolled in departments that no longer exist.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Departments table:
+------+--------------------------+
| id   | name                     |
+------+--------------------------+
| 1    | Electrical Engineering   |
| 7    | Computer Engineering     |
| 13   | Bussiness Administration |
+------+--------------------------+
Students table:
+------+----------+---------------+
| id   | name     | department_id |
+------+----------+---------------+
| 23   | Alice    | 1             |
| 1    | Bob      | 7             |
| 5    | Jennifer | 13            |
| 2    | John     | 14            |
| 4    | Jasmine  | 77            |
| 3    | Steve    | 74            |
| 6    | Luis     | 1             |
| 8    | Jonathan | 7             |
| 7    | Daiana   | 33            |
| 11   | Madelynn | 1             |
+------+----------+---------------+
Output:
+------+----------+
| id   | name     |
+------+----------+
| 2    | John     |
| 7    | Daiana   |
| 4    | Jasmine  |
| 3    | Steve    |
+------+----------+
Explanation:
John, Daiana, Steve, and Jasmine are enrolled in departments 14, 33, 74, and 77 respectively. department 14, 33, 74, and 77 do not exist in the Departments table.

思路

要找的是所在院系已经不存在的学生，也就是学生的 department_id 在 Departments 表里找不到对应的 id。

这是典型的反连接（anti-join）：从 Students 出发，找"在 Departments 里没有匹配"的行。有三种常见写法：LEFT JOIN ... IS NULL、NOT EXISTS、NOT IN。

1. # Write your MySQL query statement below
   
2. SELECT s.id, s.name
   
3. FROM Students s
   
4. LEFT JOIN Departments d
   
5.     ON s.department_id = d.id
   
6. WHERE d.id IS NULL;

复制代码

1341. Movie Rating
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Movies

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
movie_id is the primary key (column with unique values) for this table.
title is the name of the movie.
Each movie has a unique title.
Table: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+
user_id is the primary key (column with unique values) for this table.
The column 'name' has unique values.
Table: MovieRating

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+
(movie_id, user_id) is the primary key (column with unique values) for this table.
This table contains the rating of a movie by a user in their review.
created_at is the user's review date.


Write a solution to:

Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.
The result format is in the following example.



Example 1:

Input:
Movies table:
+-------------+--------------+
| movie_id    |  title       |
+-------------+--------------+
| 1           | Avengers     |
| 2           | Frozen 2     |
| 3           | Joker        |
+-------------+--------------+
Users table:
+-------------+--------------+
| user_id     |  name        |
+-------------+--------------+
| 1           | Daniel       |
| 2           | Monica       |
| 3           | Maria        |
| 4           | James        |
+-------------+--------------+
MovieRating table:
+-------------+--------------+--------------+-------------+
| movie_id    | user_id      | rating       | created_at  |
+-------------+--------------+--------------+-------------+
| 1           | 1            | 3            | 2020-01-12  |
| 1           | 2            | 4            | 2020-02-11  |
| 1           | 3            | 2            | 2020-02-12  |
| 1           | 4            | 1            | 2020-01-01  |
| 2           | 1            | 5            | 2020-02-17  |
| 2           | 2            | 2            | 2020-02-01  |
| 2           | 3            | 2            | 2020-03-01  |
| 3           | 1            | 3            | 2020-02-22  |
| 3           | 2            | 4            | 2020-02-25  |
+-------------+--------------+--------------+-------------+
Output:
+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+
Explanation:
Daniel and Monica have rated 3 movies ("Avengers", "Frozen 2" and "Joker") but Daniel is smaller lexicographically.
Frozen 2 and Joker have a rating average of 3.5 in February but Frozen 2 is smaller lexicographically.

思路

这题要回答两个互不相关的问题，每个问题只取一个结果：

评分次数最多的用户：按用户统计评分条数，次数相同时取名字字典序更小的。
2020 年 2 月平均分最高的电影：只统计 2 月的评分，按电影求平均，平均分相同时取片名字典序更小的。

两个问题的套路一样：分组 → 排序（先按主指标降序，再按名字升序）→ 取第一条。最后把两个结果上下拼成一列 results。

1. # Write your MySQL query statement below
   
2. (
   
3.     SELECT u.name AS results
   
4.     FROM MovieRating r
   
5.     JOIN Users u ON r.user_id = u.user_id
   
6.     GROUP BY u.user_id, u.name
   
7.     ORDER BY COUNT(*) DESC, u.name ASC
   
8.     LIMIT 1
   
9. )
   
10. UNION ALL
    
11. (
    
12.     SELECT m.title
    
13.     FROM MovieRating r
    
14.     JOIN Movies m ON r.movie_id = m.movie_id
    
15.     WHERE r.created_at >= '2020-02-01'
    
16.       AND r.created_at <  '2020-03-01'
    
17.     GROUP BY m.movie_id, m.title
    
18.     ORDER BY AVG(r.rating) DESC, m.title ASC
    
19.     LIMIT 1
    
20. );

复制代码

1336. Number of Transactions per Visit
Hard
Topics
SQL Schema
Pandas Schema
Table: Visits

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| visit_date    | date    |
+---------------+---------+
(user_id, visit_date) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that user_id has visited the bank in visit_date.


Table: Transactions

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| user_id          | int     |
| transaction_date | date    |
| amount           | int     |
+------------------+---------+
This table may contain duplicates rows.
Each row of this table indicates that user_id has done a transaction of amount in transaction_date.
It is guaranteed that the user has visited the bank in the transaction_date.(i.e The Visits table contains (user_id, transaction_date) in one row)


A bank wants to draw a chart of the number of transactions bank visitors did in one visit to the bank and the corresponding number of visitors who have done this number of transaction in one visit.

Write a solution to find how many users visited the bank and didn't do any transactions, how many visited the bank and did one transaction, and so on.

The result table will contain two columns:

transactions_count which is the number of transactions done in one visit.
visits_count which is the corresponding number of users who did transactions_count in one visit to the bank.
transactions_count should take all values from 0 to max(transactions_count) done by one or more users.

Return the result table ordered by transactions_count.

The result format is in the following example.



Example 1:


Input:
Visits table:
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1       | 2020-01-01 |
| 2       | 2020-01-02 |
| 12      | 2020-01-01 |
| 19      | 2020-01-03 |
| 1       | 2020-01-02 |
| 2       | 2020-01-03 |
| 1       | 2020-01-04 |
| 7       | 2020-01-11 |
| 9       | 2020-01-25 |
| 8       | 2020-01-28 |
+---------+------------+
Transactions table:
+---------+------------------+--------+
| user_id | transaction_date | amount |
+---------+------------------+--------+
| 1       | 2020-01-02       | 120    |
| 2       | 2020-01-03       | 22     |
| 7       | 2020-01-11       | 232    |
| 1       | 2020-01-04       | 7      |
| 9       | 2020-01-25       | 33     |
| 9       | 2020-01-25       | 66     |
| 8       | 2020-01-28       | 1      |
| 9       | 2020-01-25       | 99     |
+---------+------------------+--------+
Output:
+--------------------+--------------+
| transactions_count | visits_count |
+--------------------+--------------+
| 0                  | 4            |
| 1                  | 5            |
| 2                  | 0            |
| 3                  | 1            |
+--------------------+--------------+
Explanation: The chart drawn for this example is shown above.
* For transactions_count = 0, The visits (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") and (19, "2020-01-03") did no transactions so visits_count = 4.
* For transactions_count = 1, The visits (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") and (1, "2020-01-04") did one transaction so visits_count = 5.
* For transactions_count = 2, No customers visited the bank and did two transactions so visits_count = 0.
* For transactions_count = 3, The visit (9, "2020-01-25") did three transactions so visits_count = 1.
* For transactions_count >= 4, No customers visited the bank and did more than three transactions so we will stop at transactions_count = 3

思路

题目问的是"每一次访问做了几笔交易"的分布，也就是一张直方图。注意统计单位是一次访问，即 (user_id, visit_date) 这个组合，不是一个用户。

算出每次访问的交易笔数：用 Visits LEFT JOIN Transactions，按 (user_id, visit_date) 匹配。没有交易的访问也要保留，笔数是 0。
生成 0 到最大笔数的所有整数：中间可能有空缺，如示例里没有"交易 2 笔"的访问，但 transactions_count = 2 这一行仍然要输出，visits_count 为 0。所以需要一张 0 到 max 的数字表作为主表。
统计每个笔数对应的访问数：用数字表 LEFT JOIN 第 1 步的结果，数出有多少次访问。

1. # Write your MySQL query statement below
   
2. WITH RECURSIVE visit_cnt AS (
   
3.     SELECT v.user_id, v.visit_date, COUNT(t.user_id) AS cnt
   
4.     FROM Visits v
   
5.     LEFT JOIN Transactions t
   
6.         ON v.user_id = t.user_id
   
7.        AND v.visit_date = t.transaction_date
   
8.     GROUP BY v.user_id, v.visit_date
   
9. ),
   
10. nums AS (
    
11.     SELECT 0 AS n
    
12.     UNION ALL
    
13.     SELECT n + 1
    
14.     FROM nums
    
15.     WHERE n < (SELECT MAX(cnt) FROM visit_cnt)
    
16. )
    
17. SELECT
    
18.     nums.n AS transactions_count,
    
19.     COUNT(vc.cnt) AS visits_count
    
20. FROM nums
    
21. LEFT JOIN visit_cnt vc
    
22.     ON nums.n = vc.cnt
    
23. GROUP BY nums.n
    
24. ORDER BY nums.n;

复制代码

1378. Replace Employee ID With The Unique Identifier
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains the id and the name of an employee in a company.


Table: EmployeeUNI

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| unique_id     | int     |
+---------------+---------+
(id, unique_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table contains the id and the corresponding unique id of an employee in the company.


Write a solution to show the unique ID of each user, If a user does not have a unique ID replace just show null.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Employees table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 7  | Bob      |
| 11 | Meir     |
| 90 | Winston  |
| 3  | Jonathan |
+----+----------+
EmployeeUNI table:
+----+-----------+
| id | unique_id |
+----+-----------+
| 3  | 1         |
| 11 | 2         |
| 90 | 3         |
+----+-----------+
Output:
+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+
Explanation:
Alice and Bob do not have a unique ID, We will show null instead.
The unique ID of Meir is 2.
The unique ID of Winston is 3.
The unique ID of Jonathan is 1.

思路

要展示每位员工的 unique_id，没有 unique_id 的员工显示 null。

所以以 Employees 为主表做 LEFT JOIN，连接 EmployeeUNI：

员工在 EmployeeUNI 里有记录，就带出对应的 unique_id。
没有记录（如 Alice、Bob），unique_id 自动是 NULL，不需要额外处理。

如果用 INNER JOIN，没有 unique_id 的员工会直接消失，这是最容易错的地方。

1. SELECT
   
2.     u.unique_id,
   
3.     e.name
   
4. FROM Employees e
   
5. LEFT JOIN EmployeeUNI u
   
6.     ON e.id = u.id;

复制代码

1384. Total Sales Amount by Year
Hard
Topics
SQL Schema
Pandas Schema
Table: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
+---------------+---------+
product_id is the primary key (column with unique values) for this table.
product_name is the name of the product.


Table: Sales

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| product_id          | int     |
| period_start        | date    |
| period_end          | date    |
| average_daily_sales | int     |
+---------------------+---------+
product_id is the primary key (column with unique values) for this table.
period_start and period_end indicate the start and end date for the sales period, and both dates are inclusive.
The average_daily_sales column holds the average daily sales amount of the items for the period.
The dates of the sales years are between 2018 to 2020.


Write a solution to report the total sales amount of each item for each year, with corresponding product_name, product_id, report_year, and total_amount.

Return the result table ordered by product_id and report_year.

The result format is in the following example.



Example 1:

Input:
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1          | LC Phone     |
| 2          | LC T-Shirt   |
| 3          | LC Keychain  |
+------------+--------------+
Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end  | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1          | 2019-01-25   | 2019-02-28  | 100                 |
| 2          | 2018-12-01   | 2020-01-01  | 10                  |
| 3          | 2019-12-01   | 2020-01-31  | 1                   |
+------------+--------------+-------------+---------------------+
Output:
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+
Explanation:
LC Phone was sold for the period of 2019-01-25 to 2019-02-28, and there are 35 days for this period. Total amount 35*100 = 3500.
LC T-shirt was sold for the period of 2018-12-01 to 2020-01-01, and there are 31, 365, 1 days for years 2018, 2019 and 2020 respectively.
LC Keychain was sold for the period of 2019-12-01 to 2020-01-31, and there are 31, 31 days for years 2019 and 2020 respectively.

思路

每条销售记录是一段跨越若干天的区间，可能横跨多个年份。题目要的是把它按年份拆开，每年的销售额 = 该区间落在这一年的天数 × average_daily_sales。

做法分三步：

造一张年份表：题目说明日期只在 2018 到 2020 年之间，所以只有 3 个年份，每个年份有起点（1 月 1 日）和终点（12 月 31 日）。
把销售区间和年份配对：只保留两者有重叠的组合。两个区间 [a, b] 和 [c, d] 有重叠，当且仅当 a <= d 且 b >= c。
算重叠的天数：重叠部分是 [max(区间起点, 年初), min(区间终点, 年末)]，天数 = 终点 - 起点 + 1。两端都是闭区间，所以要加 1。

1. # Write your MySQL query statement below
   
2. WITH years AS (
   
3.     SELECT '2018' AS report_year, DATE('2018-01-01') AS y_start, DATE('2018-12-31') AS y_end
   
4.     UNION ALL
   
5.     SELECT '2019', DATE('2019-01-01'), DATE('2019-12-31')
   
6.     UNION ALL
   
7.     SELECT '2020', DATE('2020-01-01'), DATE('2020-12-31')
   
8. )
   
9. SELECT
   
10.     s.product_id,
    
11.     p.product_name,
    
12.     y.report_year,
    
13.     (DATEDIFF(LEAST(s.period_end, y.y_end),
    
14.               GREATEST(s.period_start, y.y_start)) + 1)
    
15.         * s.average_daily_sales AS total_amount
    
16. FROM Sales s
    
17. JOIN Product p
    
18.     ON s.product_id = p.product_id
    
19. JOIN years y
    
20.     ON s.period_start <= y.y_end
    
21.    AND s.period_end   >= y.y_start
    
22. ORDER BY s.product_id, y.report_year;

复制代码