【MySQL LeetCode】102题学习整理中

---

1. 重复的电子邮件：

编写一个解决方案来报告所有重复的电子邮件。请注意，保证电子邮件字段不为 NULL。以任意顺序返回结果表。结果格式如下例所示。
示例1：
输入:
Person table:

id	email
1	a@b.com
2	c@d.com
3	a@b.com

输出:

Email
a@b.com

解释： a@b.com is repeated two times.

解决方案：

SELECT email
FROM person
GROUP BY email
HAVING COUNT(*) > 1;

2. 游戏玩法分析

活动表 (Activity)

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

在 SQL 中，表的主键是 (player_id, event_date)。

这张表展示了一些游戏玩家在游戏平台上的行为活动。每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。

查询每位玩家第一次登录平台的日期。

示例1：

Activity 表:

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-05-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

结果表:

player_id	first_login
1	2016-03-01
2	2017-06-25
3	2016-03-02

解决方案

SELECT player_id, MIN(event_date) AS first_login
FROM activity
GROUP BY player_id;

3. 获得最高回答率问题

从具有以下列的表 survey_log 中获取回答率最高的问题：uid、action、question_id、answer_id、q_num、timestamp。

• uid 表示用户 ID；
• action 有以下几种值：show、answer、skip；
• 当action列为answer时answer_id不为null，而show和skip为null；
• q_num 是当前会话中问题的数字顺序。
  编写一个sql查询来找出回答率最高的问题。

Input:

uid	action	question_id	answer_id	q_num	timestamp
5	show	285	null	1	123
5	answer	285	124124	1	124
5	show	369	null	2	125
5	skip	369	null	2	126

Output:

survey_log
285

Explanation:
问题 285 的答对率为 1/1，而问题 369 的答对率为 0/1，因此输出结果为 285。

解决方案

SELECT TOP 1 question_id AS survey_log
FROM survey_log
GROUP BY question_id
ORDER BY COUNT(answer_id)*1.0/(COUNT(*)-COUNT(answer_id)) DESC;

4. 寻找客户推荐人

Table: Customer

Column Name	Type
id	int
name	varchar
referee_id	int

在 SQL 中，"id "是该表的主键列。表中的每一行都显示客户的 id 、客户的 name 和推荐客户的 id 。

查找不是由客户通过 id = 2 引用的客户姓名。以任意顺序返回结果表。结果格式如下例所示。

示例 1:

Input:

Customer table:

id	name	referee_id
1	Will	null
2	Jane	null
3	Alex	2
4	Bill	null
5	Zack	1
6	Mark	2

Output:

name
Will
Jane
Bill
Zack

解决方案

-- Solution 1: Basics
SELECT name 
FROM customer
WHERE ISNULL(referee_id,0) != 2;


-- Solution 2: Basics
SELECT name 
FROM customer
WHERE COALESCE(referee_id,0) != 2;


-- Solution 3: Basics
SELECT name
FROM customer
WHERE referee_id != 2 OR referee_id IS NULL;

5. 下单数量最多的顾客

Table: Orders

Column Name	Type
order_number	int
customer_number	int

order_number 是该表的主键（具有唯一值的列）。该表包含有关订单 ID 和客户 ID 的信息。

编写一个解决方案来查找下订单数量最多的客户的 customer_number 。生成测试用例是为了确保一个客户下的订单比任何其他客户都多。结果格式如下例所示。

示例1:

Input:

Orders table:

order_number	customer_number
1	1
2	2
3	3
4	3

Output:

customer_number
3

Explanation:

编号为 3 的客户有两个订单，大于客户 1 或客户 2，因为他们每人只有一个订单。因此，结果是 “客户编号 3”。

追问：
如果不止一个客户的订单数量最多，在这种情况下，您能找到所有的 `客户编号’吗？

解决方案

SELECT TOP 1 customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC;

6. 大国

Table: World

Column Name	Type
name	varchar
continent	varchar
area	int
population	int
gdp	bigint

name "是该表的主键（具有唯一值的列）。该表的每一行都提供了一个国家的名称、所属大洲、面积、人口和 GDP 值等信息。

如果出现以下情况，一个国家就是大国

1. 面积至少有 300 万（即 3000000 平方公里），或
2. 人口至少有两千五百万（即 25000000）。

写出一个解决方案，找出大国的名称、人口和面积。按任意顺序返回结果表。
结果格式见下例。

示例 1 :

Input:

World table:

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000

Output:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

解决方案

SELECT name, population, area
FROM World
WHERE area > 3000000 OR population > 25000000;

7. 超过 5 名学生的班级

Table: Courses

Column Name	Type
student	varchar
class	varchar

(student, class) 是此表的主键（具有唯一值的列组合）。该表的每一行都显示了学生的姓名和所在班级。

写出一个解决方案，找出所有至少有 5 名学生的班级。以任意顺序返回结果表。结果格式如下例所示。

示例 1：

Input:

Courses table:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

Output:

class
Math

Explanation:

• 数学有 6 名学生，因此我们将其包括在内。
• 英语有 1 名学生，所以不包括在内。
• 生物有 1 名学生，因此我们不将其包括在内。
• 计算机有 1 名学生，因此不包括在内。

解决方案

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

8. 最大的单个数字

Table: MyNumbers

Column Name	Type
num	int

该表可能包含重复数据（换句话说，该表在 SQL 中没有主键）。该表的每一行都包含一个整数。

单一数字是指只在 MyNumbers 表中出现过一次的数字。找出最大的单个数字。如果没有单个数字，则报告 null。结果格式如下例所示。

示例1 :

Input:

MyNumbers table:

num
8
8
3
3
1
4
5
6

Output:

num
6

Explanation:
单个数字是 1、4、5 和 6。
由于 6 是最大的单个数字，因此我们返回它。

示例 2 :

Input:

MyNumbers table:

num
8
8
7
7
3
3
3

Output:

num
null

Explanation:
输入表中没有单个数字，因此我们返回 null。

解决方案

SELECT MAX(num) AS num
FROM (
    -- Select numbers appear only once
    SELECT num 
    FROM my_numbers 
    GROUP BY num 
    HAVING COUNT(*) = 1
) tb1;

9. 不无聊的电影

Table: Cinema

| Column Name | Type |
|加粗样式-------------|---------|
| id | int |
| movie | varchar |
| description | varchar |
| rating | float |

id 是该表的主键（具有唯一值的列）。每一行都包含电影名称、类型和评分信息。rating是一个小数点后 2 位的浮点数，取值范围为 [0, 10] 。

编写一个解决方案，报告 ID 为奇数且描述不 "无聊 "的电影。返回按评分降序排列的结果表。结果格式如下例所示。

示例 1：

Input:

Cinema table:

id	movie	description	rating
1	War	great 3D	8.9
2	Science	fiction	8.5
3	irish	boring	6.2
4	Ice song	Fantacy	8.6
5	House card	Interesting	9.1

Output:

id	movie	description	rating
5	House card	Interesting	9.1
1	War	great 3D	8.9

Explanation:
我们有三部 ID 为奇数的电影：1、3 和 5。ID = 3 的电影很无聊，因此我们不将其列入答案。

解决方案

SELECT *
FROM cinema
WHERE id % 2 = 1 AND description != 'boring'
ORDER BY rating DESC;

10. 至少合作三次的演员和导演

Table: ActorDirector

Column Name	Type
actor_id	int
director_id	int
timestamp	int

timestamp是该表的主键（具有唯一值的列）。写一个解决方案，找出演员与导演至少合作过三次的所有配对（actor_id, director_id）。按任意顺序返回结果表。结果格式如下例所示。

示例 1：

Input:
ActorDirector table:

actor_id	director_id	timestamp
1	1	0
1	1	1
1	1	2
1	2	3
1	2	4
2	1	5
2	1	6

Output:

actor_id	director_id
1	1

Explanation:
唯一的一对是（1，1），他们正好合作了 3 次。

解决方案

SELECT actor_id, director_id
FROM ActorDirector
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3;

11. 产品销售分析

Table: Sales

Column Name	Type
sale_id	int
product_id	int
year	int
quantity	int
price	int

sale_id 是该表的主键。product_id是 "Product"表的外键。请注意，price是按单位计算的。

Table: Product

Column Name	Type
product_id	int
product_name	varchar

product_id 是该表的主键。编写一条 SQL 查询，报告每个产品 ID 的总销售量。查询结果格式如下：

Sales table:

sale_id	product_id	year	quantity	price
1	100	2008	10	5000
2	100	2009	12	5000
7	200	2011	15	9000

Product table:

product_id	product_name
100	Nokia
200	Apple
300	Samsung

Result table:

product_id	total_quantity
100	22
200	15

解决方案

SELECT product_id, SUM(quantity) AS total_quantity
FROM Sales
GROUP BY product_id;

12. 项目员工

Table: Project

Column Name	Type
project_id	int
employee_id	int

(project_id,employee_id)是该表的主键。
employee_id 是 Employee 表的外键。

Table: Employee

Column Name	Type
employee_id	int
name	varchar
experience_years	int

employee_id 是该表的主键。编写一个 SQL 查询，报告所有拥有最多雇员的项目。查询结果格式如下：

Project table:

project_id	employee_id
1	1
1	2
1	3
2	1
2	4

Employee table:

employee_id	name	experience_years
1	Khaled	3
2	Ali	2
3	John	1
4	Doe	2

Result table:

project_id
1

第一个项目有 3 名员工，第二个项目有 2 名员工，因此第一个项目是员工人数最多的项目。

解决方案

SELECT TOP 1 WITH TIES project_id
FROM Project
GROUP BY project_id
ORDER BY COUNT(employee_id) DESC;

13. 销售分析

Table: Product

Column Name	Type
product_id	int
product_name	varchar
unit_price	int

product_id是该表的主键。

Table: Sales

Column Name	Type
seller_id	int
product_id	int
buyer_id	int
sale_date	date
quantity	int
price	int

该表没有主键，可以有重复行。product_id是 Product表的外键。

编写一个 SQL 查询，按总销售价格报告最畅销的产品。如果出现并列，则全部报告。查询结果格式如下：

Product table:

product_id	product_name	unit_price
1	S8	1000
2	G4	800
3	iPhone	1400

Sales table:

seller_id	product_id	buyer_id	sale_date	quantity	price
1	1	1	2019-01-21	2	2000
1	2	2	2019-02-17	1	800
2	2	3	2019-06-02	1	800
3	3	4	2019-05-13	2	2800

Result table:

seller_id
1
3

id为 1 和 3 的卖家都卖出了总价最高的 2800 件产品。

解决方案

SELECT TOP 1 WITH TIES seller_id
FROM Sales
GROUP BY seller_id
ORDER BY SUM(price) DESC;

14. 过去 30 天的用户活动

Table: Activity

Column Name	Type
user_id	int
session_id	int
activity_date	date
activity_type	enum

此表可能有重复行。
activity_type 列是一个 ENUM（类别）类 (open_session, end_session, scroll_down, send_message).
该表显示了一个社交媒体网站的用户活动。
请注意，每个会话只属于一个用户。

写一个解决方案，找出截至 2019-07-27 日（含）的 30 天内的每日活跃用户数。如果用户在某天至少进行了一次活动，则该用户在该天为活跃用户。以任意顺序返回结果表。结果格式如下例所示。

示例 1：

Input:
Activity table:

user_id	session_id	activity_date	activity_type
1	1	2019-07-20	open_session
1	1	2019-07-20	scroll_down
1	1	2019-07-20	end_session
2	4	2019-07-20	open_session
2	4	2019-07-21	send_message
2	4	2019-07-21	end_session
3	2	2019-07-21	open_session
3	2	2019-07-21	send_message
3	2	2019-07-21	end_session
4	3	2019-06-25	open_session
4	3	2019-06-25	end_session

Output:

day	active_users
2019-07-20	2
2019-07-21	2

Explanation:
请注意，我们并不关心活跃用户为零的天数。

解决方案

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
GROUP BY activity_date
HAVING activity_date BETWEEN  DATEADD(day,-29,'2019-07-27') and '2019-07-27';

15. 文章浏览量

Table: Views

Column Name	Type
article_id	int
author_id	int
viewer_id	int
view_date	date

该表没有主键（具有唯一值的列），因此可能有重复行。
该表中的每一行都表示某个读者在某个日期浏览了某篇文章（由某个作者撰写）。
请注意，相同的 author_id 和 viewer_id 表示同一个人。

写一个解决方案，找出所有至少浏览过一篇自己文章的作者。返回按 id 升序排序的结果表。结果格式如下。

示例 1：

Input:
Views table:

article_id	author_id	viewer_id	view_date
1	3	5	2019-08-01
1	3	6	2019-08-02
2	7	7	2019-08-01
2	7	6	2019-08-02
4	7	1	2019-07-22
3	4	4	2019-07-21
3	4	4	2019-07-21

Output:

id
4
7

解决方案

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id;

16. 文章浏览量

Table: Views

Column Name	Type
article_id	int
author_id	int
viewer_id	int
view_date	date

该表没有主键，可能有重复行。该表中的每一行都表示某个读者在某个日期浏览了某篇文章（由某个作者撰写）。请注意，相同的 author_id 和 viewer_id 表示同一个人。

编写一条 SQL 查询，找出所有在同一日期浏览过一篇以上文章的人，并按其 id 升序排序。查询结果格式如下：

Views table:

article_id	author_id	viewer_id	view_date
1	3	5	2019-08-01
3	4	5	2019-08-01
1	3	6	2019-08-02
2	7	7	2019-08-01
2	7	6	2019-08-02
4	7	1	2019-07-22
3	4	4	2019-07-21
3	4	4	2019-07-21

Result table:

id
5
6

ID 为 5 和 6 的用户在 2019-08-01 浏览了不止一篇文章。

解决方案

SELECT DISTINCT viewer_id AS id
FROM Views
GROUP BY viewer_id, view_date
-- When viewer viewed the same article more than once in the same day, using DISTINCT could avoid count that article twice
HAVING COUNT(DISTINCT article_id) > 1
ORDER BY id;

17. 删除重复邮件

Table: Person

Column Name	Type
id	int
email	varchar

id 是该表的主键（具有唯一值的列）。
该表的每一行都包含一个电子邮件。电子邮件不包含大写字母。

请编写一个解决方案，删除所有重复的电子邮件，只保留一个具有最小 id 的唯一电子邮件。

对于 SQL 用户，请注意应编写 DELETE 语句，而不是 SELECT 语句。

个人表的最终顺序并不重要。

结果格式如下例所示。

示例 1：

Input:
Person table:

id	email
1	john@example.com
2	bob@example.com
3	john@example.com

Output:

id	email
1	john@example.com
2	bob@example.com

Explanation:
john@example.com 重复两次。我们保留最小 Id = 1 的一行。

解决方案

-- Solution 1: SQL Command, Join
DELETE p1 
FROM person p1
JOIN person p2
    ON p1.email = p2.email AND p1.id > p2.id;  
    


-- Solution 2: SQL Command, Subquery
DELETE FROM person
WHERE id NOT IN (
    SELECT *
    FROM (
        SELECT MIN(id) AS id
        FROM person
        GROUP BY email
        ) tb1
);

18. 交换工资

Table: Salary

Column Name	Type
id	int
name	varchar
sex	ENUM
salary	int

id 是该表的主键（具有唯一值的列）。
sex 列是类型为('m', 'f')的 ENUM（类别）值。
该表包含一名雇员的信息。

编写一个解决方案，用一条更新语句交换所有的 "f "和 "m "值（即把所有的 "f "值改为 “m”，反之亦然），并且不使用中间临时表。请注意，您必须编写一条更新语句，不要为这个问题编写任何选择语句。结果格式如下例所示。

示例 1：

Input:
Salary table:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

Output:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

Explanation:
(1、A）和（3、C）中的 "m "改为 “f”。
(2、B）和（4、D）中的 "f "改为 “m”。

解决方案

UPDATE salary
SET sex = CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END;

19. 组合两个表

表: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

personId 是该表的主键（具有唯一值的列）。该表包含一些人的 ID 和他们的姓和名的信息。

表: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

addressId 是该表的主键（具有唯一值的列）。该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。

编写解决方案，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。以 任意顺序 返回结果表。结果格式如下所示。

示例 1:

输入:

Person表:

personId	lastName	firstName
1	Wang	Allen
2	Alice	Bob

Address表:

addressId	personId	city	state
1	2	New York City	New York
2	3	Leetcode	California

输出:

firstName	lastName	city	state
Allen	Wang	Null	Null
Bob	Alice	New York City	New York

解释:
地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。addressId = 1 包含了 personId = 2 的地址信息。

解决方案

SELECT p.firstname, p.lastname, a.city, a.state
FROM person p
-- LEFT JOIN return all records from the left table, and the matched records from the right table
LEFT JOIN address a
  ON p.personid = a.personid;

20. 找出薪水比经理高的员工

Table: Employee

Column Name	Type
id	int
name	varchar
salary	int
managerId	int

id 是该表的主键（具有唯一值的列）。该表的每一行都包含员工的 ID、姓名、工资及其经理的 ID。

编写一个解决方案，找出收入高于其经理的员工。按任意顺序返回结果表。结果格式如下例所示。

例 1：

Input:
Employee table:

id	name	salary	managerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	Null
4	Max	90000	Null

Output:

Employee
Joe

解释： 乔是唯一一个收入比经理高的员工。

解决方案

SELECT e1.name AS Employee
FROM employee e1
JOIN employee e2
    ON e1.Managerid = e2.id
WHERE e1.salary > e2.salary;

21. 从来没有下过订单的顾客

Table: Customers

Column Name	Type
id	int
name	varchar

id 是该表的主键（具有唯一值的列）。该表的每一行都表示一位客户的 ID 和姓名。

Table: Orders

Column Name	Type
id	int
customerId	int

id 是该表的主键（具有唯一值的列）。customerId 是客户表 ID 的外键（引用列）。该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。

编写一个解决方案，找出所有从未订购过任何东西的客户。按任意顺序返回结果表。结果格式如下例所示。

Example 1:

Input:
Customers table:

id	name
1	Joe
2	Henry
3	Sam
4	Max

Orders table:

id	customerId
1	3
2	1

Output:

Customers
Henry
Max

解决方案

SELECT c.name AS customers
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customerid
WHERE o.id IS NULL;

22. 部门最高薪水

Table: Employee

Column Name	Type
id	int
name	varchar
salary	int
departmentId	int

id 是该表的主键（具有唯一值的列）。departmentId 是部门表中 ID 的外键（引用列）。该表的每一行都显示了员工的 ID、姓名和工资。它还包含其部门的 ID。

Table: Department

Column Name	Type
id	int
name	varchar

id 是该表的主键（具有唯一值的列）。保证部门名称不是空值。该表的每一行都表示一个部门的 ID 及其名称。

编写一个解决方案，找出每个部门中工资最高的员工。按任意顺序返回结果表。结果格式如下例所示。

Example 1:
Input:
Employee table:

id	name	salary	departmentId
1	Joe	70000	1
2	Jim	90000	1
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1

Department table:

id	name
1	IT
2	Sales

Output:

Department	Employee	Salary
IT	Jim	90000
Sales	Henry	80000
IT	Max	90000

解释：麦克斯和吉姆都是信息技术部门工资最高的人，而亨利是销售部门工资最高的人。

解决方案

WITH max_salary AS (
    SELECT departmentid, max(salary) AS max_salary
    FROM employee
    GROUP BY departmentid
)

SELECT d.name department, e.name employee, e.salary
FROM max_salary ms
JOIN employee e
    ON ms.departmentid = e.departmentid AND ms.max_salary = e.salary
JOIN department d
  ON e.departmentid = d.id;

23. 气温比前一天高

Table: Weather

Column Name	Type
id	int
recordDate	date
temperature	int

id 是该表具有唯一值的列。没有记录日期相同的不同行。该表包含某天的温度信息。

编写一个解决方案，找出与前一天（昨天）相比温度较高的所有日期的 Id。以任意顺序返回结果表。结果格式见下例。

Example 1:

Input:
Weather table:

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

Output:

id
2
4

Explanation:
2015-01-02，气温高于前一天（10 -> 25）。
2015-01-04，气温高于前一天（20 -> 30）。

解决方案

SELECT w2.id
FROM weather w1
JOIN weather w2
    ON DATEADD(day,1,w1.recorddate) = w2.recorddate
WHERE w1.temperature < w2.temperature;

24. 行程和用户

Table: Trips

Column Name	Type
id	int
client_id	int
driver_id	int
city_id	int
status	enum
request_at	date

id 是该表的主键（具有唯一值的列）。该表保存所有出租车行程。每个行程都有一个唯一的 id，而客户_id 和司机_id 是用户表中 users_id 的外键。状态是一个 ENUM（类别）类型 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

Table: Users

Column Name	Type
users_id	int
banned	enum
role	enum

users_id 是该表的主键（具有唯一值的列）。该表包含所有用户。每个用户都有唯一的 users_id，角色是 ENUM 类型（“客户”、“司机”、“合作伙伴”）。禁止是一个 ENUM（类别）类型（“是”、“否”）。

取消率的计算方法是：当天未被禁用用户的（客户或司机）取消请求数除以未被禁用用户的总请求数。

请写出一个解决方案，求出 "2013-10-01 "至 "2013-10-03 "期间每天未被禁用用户（客户端和驱动程序都必须未被禁用）的请求取消率。将取消率四舍五入到小数点后两位。按任意顺序返回结果表。

结果格式如下。

Example 1:

Input:
Trips table:

id	client_id	driver_id	city_id	status	request_at
1	1	10	1	completed	2013-10-01
2	2	11	1	cancelled_by_driver	2013-10-01
3	3	12	6	completed	2013-10-01
4	4	13	6	cancelled_by_client	2013-10-01
5	1	10	1	completed	2013-10-02
6	2	11	6	completed	2013-10-02
7	3	12	6	completed	2013-10-02
8	2	12	12	completed	2013-10-03
9	3	10	12	completed	2013-10-03
10	4	13	12	cancelled_by_driver	2013-10-03

Users table:

users_id	banned	role
1	No	client
2	Yes	client
3	No	client
4	No	client
10	No	driver
11	No	driver
12	No	driver
13	No	driver

Output:

Day	Cancellation Rate
2013-10-01	0.33
2013-10-02	0.00
2013-10-03	0.50

Explanation:
2013-10-01：

• 共有 4 个请求，其中 2 个被取消。
• 但是，Id=2 的请求是由一个被禁用的客户端（User_Id=2）提出的，因此在计算中被忽略。
• 因此，共有 3 个未被禁用的请求，其中 1 个被取消。
• 取消率为 (1 / 3) = 0.33
  在 2013-10-02：
• 共有 3 个请求，其中 0 个被取消。
• Id=6 的请求是由一个被禁用的客户端提出的，因此被忽略。
• 因此，共有 2 个未被禁用的请求，其中 0 个被取消。
• 取消率为 (0 / 2) = 0.00
  2013-10-03：
• 共有 3 个请求，其中 1 个被取消。
• Id=8 的请求是由被禁用的客户端提出的，因此被忽略。
• 因此，共有 2 个未被禁用的请求，其中 1 个被取消。
• 取消率为 (1 / 2) = 0.50

解决方案

SELECT t.Request_at AS 'Day', 
    CAST(
        AVG(CASE 
                WHEN status = 'completed' then 0
                ELSE 1.0
            END)
        AS DECIMAL(3,2)
    ) AS 'Cancellation Rate'
FROM Trips AS t
INNER JOIN Users c 
    ON t.Client_Id=c.Users_Id
INNER JOIN Users d 
    ON t.Driver_Id=d.Users_Id
-- filter our banned client and driver and limit time frame
WHERE c.Banned='no' AND
      d.Banned='no' AND
      t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at;

25. 游戏数据分析

Table: Activity

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

(player_id，event_date）是该表的主键。
该表显示了某款游戏的玩家活动。
每一行都是一个玩家的记录，该玩家在某天使用某个设备登录并玩了若干游戏（可能为 0），然后注销。

编写一个 SQL 查询，报告每个玩家首次登录的设备。

查询结果格式如下：

Activity table:

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-05-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

Result table:

player_id	device_id
1	2
2	3
3	1

解决方案

SELECT a.player_id, device_id
FROM Activity a
JOIN (
    SELECT player_id, MIN(event_date) min_date
    FROM Activity
    GROUP BY player_id
    ) tb1
ON a.player_id = tb1.player_id AND a.event_date = tb1.min_date;

26. 游戏数据分析

Table: Activity

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

(player_id，event_date）是该表的主键（具有唯一值的列组合）。此表显示某些游戏的玩家活动。
每一行都是一个玩家的记录，该玩家登录并玩了若干游戏（可能为 0），然后在某天使用某种设备注销。

请写一个解决方案，以报告在首次登录后的第二天再次登录的玩家比例，四舍五入到小数点后 2 位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家人数，然后用该人数除以玩家总数。

结果格式如下。

Example 1:

Input:
Activity table:

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

Output:

fraction
0.33

Explanation:
只有id为1的玩家在登录第一天后重新登录，因此答案为 1/3 = 0.33

解决方案

SELECT CAST(
    -- using DISTINCT to avoid double counting
    COUNT(DISTINCT a2.player_id)*1.0/COUNT(DISTINCT a1.player_id)
    AS DECIMAL(3,2)
) AS fraction
FROM (
    -- get the first-logged-in date of each player
    SELECT player_id, MIN(event_date) AS event_date
    FROM Activity
    GROUP BY player_id
) a1
-- if a player logged back in on the day right after the first-logged-in date, 
-- he/she would get a matched record from table a2
LEFT JOIN Activity a2
ON a1.player_id = a2.player_id AND DATEADD(day,1,a1.event_date) = a2.event_date;

27. 拥有至少 5 名直接下属的经理

Table: Employee

Column Name	Type
id	int
name	varchar
department	varchar
managerId	int

id 是该表的主键（具有唯一值的列）。
该表的每一行都显示了员工姓名、部门及其经理的 id。
如果 managerId 为空，则表示该员工没有经理。
没有员工会成为自己的经理。

编写一个解决方案，查找至少有五名直接下属的经理。
以任意顺序返回结果表。

结果格式如下。

Example 1:

Input:
Employee table:

id	name	department	managerId
101	John	A	null
102	Dan	A	101
103	James	A	101
104	Amy	A	101
105	Anne	A	101
106	Ron	B	101

Output:

name
John

解决方案

SELECT e2.name
FROM employee e1
JOIN employee e2
ON e1.managerid = e2.id
GROUP BY e2.id, e2.name
HAVING COUNT(e1.id) >= 5;

28. 赢家候选

Table: Candidate

id	Name
1	A
2	B
3	C
4	D
5	E

Table: Vote

id	CandidateId
1	2
2	4
3	3
4	2
5	5

id 是自动递增主键、
CandidateId 是候选人表中出现的 id。

编写一个 sql 查找获胜候选人的姓名，上面的示例将返回获胜者 B。

Name
B

Notes:

你可以假设没有平局，换句话说，最多会有一个获胜的候选人。

解决方案

SELECT TOP 1 c.Name
FROM Candidate c
JOIN Vote v
ON c.id = v.CandidateId
GROUP BY c.id, c.Name
ORDER BY COUNT(*) DESC;

29.

Table: Employee

Column Name	Type
empId	int
name	varchar
supervisor	int
salary	int

empId 是该表唯一值列。
该表的每一行都显示了雇员的姓名和 ID，以及他们的工资和经理 ID。

Table: Bonus

Column Name	Type
empId	int
bonus	int

empId 是该表的唯一值列。
empId 是雇员表中 empId 的外键（引用列）。
该表的每一行都包含一名雇员的 id 和他们各自的奖金。

编写一个解决方案，报告奖金少于 1000 的每位员工的姓名和奖金数额。

以任意顺序返回结果表。

结果格式如下例所示。

Example 1:

Input:
Employee table:

empId	name	supervisor	salary
3	Brad	null	4000
1	John	3	1000
2	Dan	3	2000
4	Thomas	3	4000

Bonus table:

empId	bonus
2	500
4	2000

Output:

name	bonus
Brad	null
John	null
Dan	500

解决方案

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
-- when employee has no bonus, his/her bonus will be null after left join
WHERE b.bonus IS NULL 
    OR b.bonus < 1000;

30. 统计各系学生人数

某大学使用两个数据表（学生表和院系表）来存储有关学生和各专业相关院系的数据。

编写一个查询，打印系表中所有系（即使是没有在校学生的系）各自的系名和每个系的学生人数。

按学生人数从多到少排序；如果两个或多个系的学生人数相同，则按系名的字母顺序排序。

学生表如下：

Column Name	Type
student_id	Integer
student_name	String
gender	Character
dept_id	Integer

其中 student_id 是学生的 ID 编号，student_name 是学生的姓名，gender 是学生的性别，dept_id 是与学生申报专业相关的系 ID。

系表描述如下：

Column Name	Type
dept_id	Integer
dept_name	String

其中，dept_id 是部门的 ID 编号，dept_name 是部门名称。

下面是一个输入示例：
学生表：

student_id	student_name	gender	dept_id
1	Jack	M	1
2	Jane	F	1
3	Mark	M	2

department table:

dept_id	dept_name
1	Engineering
2	Science
3	Law

The Output should be:

dept_name	student_number
Engineering	2
Science	1
Law	0

解决方案

SELECT d.dept_name, COUNT(student_id) AS student_number
FROM department d
LEFT JOIN student s
ON d.dept_id = s.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY student_number DESC, d.dept_name;

31. 销售人员

Table: SalesPerson

Column Name	Type
sales_id	int
name	varchar
salary	int
commission_rate	int
hire_date	date

sales_id 是该表的主键（具有唯一值的列）。
该表的每一行都显示销售人员的姓名和 ID，以及他们的工资、佣金率和雇用日期。

Table: Company

Column Name	Type
com_id	int
name	varchar
city	varchar

com_id 是该表的主键（具有唯一值的列）。
该表中的每一行都表示一家公司的名称和 ID 以及该公司所在的城市。

Table: Orders

Column Name	Type
order_id	int
order_date	date
com_id	int
sales_id	int
amount	int

order_id 是该表的主键（具有唯一值的列）。
com_id 是公司表中 com_id 的外键（引用列）。
sales_id 是销售人员表中 sales_id 的外键（引用列）。
该表的每一行都包含一个订单的信息。其中包括公司 ID、销售人员 ID、订单日期和支付金额。

编写一个解决方案，找出与名称为 "RED "的公司没有任何订单的所有销售人员的姓名。

按任意顺序返回结果表。

结果格式见下例。

Example 1:

Input:

SalesPerson table:

sales_id	name	salary	commission_rate	hire_date
1	John	100000	6	4/1/2006
2	Amy	12000	5	5/1/2010
3	Mark	65000	12	12/25/2008
4	Pam	25000	25	1/1/2005
5	Alex	5000	10	2/3/2007

Company table:

com_id	name	city
1	RED	Boston
2	ORANGE	New York
3	YELLOW	Boston
4	GREEN	Austin

Orders table:

order_id	order_date	com_id	sales_id	amount
1	1/1/2014	3	4	10000
2	2/1/2014	4	5	5000
3	3/1/2014	1	1	50000
4	4/1/2014	1	4	25000

Output:

name
Amy
Mark
Alex

解决方案

SELECT name
FROM salesperson
WHERE sales_id NOT IN (
    SELECT o.sales_id
    FROM orders o
    JOIN company c
    ON o.com_id = c.com_id
    WHERE c.name = 'RED'
);

32. 二级追随者

Table: Follow

Column Name	Type
followee	varchar
follower	varchar

(followee，follower）是该表的主键（具有唯一值的列组合）。
该表中的每一行都表示用户关注者在社交网络上关注了用户被关注者。
不会出现用户关注自己的情况。

二级关注者是指具备以下条件的用户

1. 至少关注一个用户，且
2. 被至少一个用户关注。

编写一个解决方案，报告二级用户及其关注者的数量。

按关注者的字母顺序返回结果表。

结果格式如下。

Example 1:

Input:
Follow table:

followee	follower
Alice	Bob
Bob	Cena
Bob	Donald
Donald	Edward

Output:

follower	num
Bob	2
Donald	1

Explanation:
用户 Bob 有 2 个关注者。鲍勃是二级关注者，因为他关注了爱丽丝，所以我们把他包括在结果表中。
用户 Donald 有 1 个关注者。唐纳德是二级关注者，因为他关注了鲍勃，所以我们将他包含在结果表中。
用户 Alice 有 1 个关注者。爱丽丝不是二级关注者，因为她没有关注任何人，所以我们没有将她包括在结果表中。

解决方案

SELECT f1.follower, COUNT(DISTINCT f2.follower) AS num
FROM follow f1
JOIN follow f2
ON f1.follower = f2.followee
GROUP BY f1.follower
ORDER BY f1.follower;

33. 平均工资：部门 VS 公司

给定以下两个表格，编写一个查询，以显示部门员工平均工资与公司平均工资的比较结果（较高/较低/相同）。

Table: salary

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_id 列指的是下表 employee 中的 employee_id。

employee_id	department_id
1	1
2	2
3	2

因此，上述样本数据的结果是

pay_month	department_id	comparison
2017-03	1	higher
2017-03	2	lower
2017-02	1	same
2017-02	2	same

Explanation：

三月份，公司的平均工资为 (9000+6000+10000)/3 = 8333.33…

部门’1’的平均工资是 9000，也就是员工编号’1’的工资，因为该部门只有一名员工。因此，比较结果是 “更高”，因为 9000 显然大于 8333.33。

部门’2’的平均工资为 (6000 + 10000)/2 = 8000，这是员工编号’2’和’3’的平均工资。因此，比较结果是 “较低”，因为 8000 < 8333.33。

用同样的公式比较二月份的平均工资，结果是 “相同”，因为部门 "1 "和 "2 "与公司的平均工资相同，都是 7000。

解决方案

-- If need to extract date in other format in the future, FORMAT() function could be used

-- Solution 1: Join, Window Function, Subquery, CASE WHEN
WITH tb1 AS (
    SELECT DISTINCT department_id, LEFT(pay_date,7) AS pay_month,
        AVG(amount) OVER (PARTITION BY department_id, LEFT(pay_date,7))AS avg_dept,
        AVG(amount) OVER (PARTITION BY LEFT(pay_date,7)) AS avg_comp
    FROM salary s
    JOIN employee e
    ON s.employee_id = e.employee_id
)

SELECT pay_month, department_id,
    CASE
        WHEN avg_dept > avg_comp THEN 'higher'
        WHEN avg_dept < avg_comp THEN 'lower'
        ELSE 'same'
    END AS comparison
FROM tb1;



-- Solution 2: Join, Subquery, CASE WHEN
WITH dept AS (
    SELECT e.department_id, LEFT(s.pay_date,7) AS pay_month, AVG(s.amount) AS avg_dept
    FROM salary s
    JOIN employee e
    ON s.employee_id = e.employee_id
    GROUP BY e.department_id, LEFT(s.pay_date,7)
),
comp AS (
    SELECT LEFT(pay_date,7) AS pay_month, AVG(amount) AS avg_comp
    FROM salary
    GROUP BY LEFT(pay_date,7)
)

SELECT d.department_id, d.pay_month,
    CASE
        WHEN d.avg_dept > c.avg_comp THEN 'higher'
        WHEN d.avg_dept < c.avg_comp THEN 'lower'
        ELSE 'same'
    END AS comparison
FROM dept d
JOIN comp c
ON d.pay_month = c.pay_month;

34.

35.

36.

37.

38.

39.

40.

41.

42.

43.

44.

45.

46.

47.

48.

49.

50.

51.

52.

53.

54.

55.

56.

57.

58.

59.

60.

61.

62.

63.

64.

65.

66.

67.

68.

69.

70.

71.

72.

73.

74.

75.

76.

77.

78.

79.

80.

81.

82.

83.

84.

85.

86.

87.

88.

89.

90.

91.

92.

93.

94.

95.

96.

97.

98.

99.

100.

101.

102.