LeetCode数据库SQL题目记录（难度：中等）

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/nth-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary 'getNthHighestSalary(2)'
      from 
      (select Salary,dense_rank() over(order by Salary desc) cnt from Employee) a
      where a.cnt = N
  );
END

178. 分数排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/rank-scores
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select Score,dense_rank() over(order by Score desc) 'Rank'  
from Scores

180. 连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| num | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按任意顺序排列。

查询结果格式如下面的例子所示：

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/consecutive-numbers
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct d.num1 ConsecutiveNums from 
(select a.Id,a.Num num1,b.Num num2,c.Num num3
from Logs a,Logs b,Logs c
where a.id = b.id - 1
  and a.id = c.id - 2) d
where d.num1 = d.num2
  and d.num1 = d.num3

184. 部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| 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 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/department-highest-salary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select b.Name Department,a.Name Employee,a.Salary   
from Employee a,Department b 
where a.DepartmentId = b.Id
  and (a.DepartmentId,a.Salary) in (select DepartmentId,max(Salary) from Employee group by DepartmentId)

534. 游戏玩法分析 III

Table: Activity

编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

查询结果格式如下所示：

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

Result table:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+-----------+------------+---------------------+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select player_id,event_date,
       sum(games_played) over(partition by player_id order by event_date) games_played_so_far 
from Activity 
order by player_id,event_date desc

550. 游戏玩法分析 IV

Table: Activity

编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

查询结果格式如下所示：

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 |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction |
+-----------+
| 0.33 |
+-----------+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-iv
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select round(sum(case when c.playerb is not null then 1 else 0 end)/
       count(distinct c.player_id),2) fraction  
from 
(select a.*,b.player_id playerb from Activity a left join
(select player_id,min(event_date) first_date from Activity group by player_id) b
on DATEDIFF(a.event_date,b.first_date) = 1
and a.player_id = b.player_id) c

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

Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。

+------+----------+-----------+----------+
|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 |
+------+----------+-----------+----------+
给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

+-------+
| Name |
+-------+
| John |
+-------+
注意:
没有人是自己的下属。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/managers-with-at-least-5-direct-reports
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select b.name from 
(select ManagerId 
from Employee 
where ManagerId is not null
group by ManagerId 
having count(*) >= 5) a,Employee b 
where a.ManagerId = b.Id

574. 当选者

表: Candidate

+-----+---------+
| id | Name |
+-----+---------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+-----+---------+
表: Vote

+-----+--------------+
| id | CandidateId |
+-----+--------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
+-----+--------------+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.
请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B.

+------+
| Name |
+------+
| B |
+------+
注意:

你可以假设没有平局，换言之，最多只有一位当选者。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/winning-candidate
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select b.Name from 
(select CandidateId,count(*) cnt from Vote group by CandidateId) a,Candidate b
where a.CandidateId = b.id  
order by a.cnt desc
limit 1

578. 查询回答率最高的问题

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。

id 表示用户 id；action 有以下几种值："show"，"answer"，"skip"；当 action 值为 "answer" 时 answer_id 非空，而 action 值为 "show" 或者 "skip" 时 answer_id 为空；q_num 表示当前会话中问题的编号。

请编写 SQL 查询来找到具有最高回答率的问题。

示例：

输入：
+------+-----------+--------------+------------+-----------+------------+
| id | 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 |
+------+-----------+--------------+------------+-----------+------------+
输出：
+-------------+
| survey_log |
+-------------+
| 285 |
+-------------+
解释：
问题 285 的回答率为 1/1，而问题 369 回答率为 0/1，因此输出 285 。

提示：回答率最高的含义是：同一问题编号中回答数占显示数的比例最高。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/get-highest-answer-rate-question
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.question_id survey_log from 
(select question_id,
       sum(case when action = 'answer' then 1 else 0 end)/
       count(distinct q_num) rate  
from survey_log 
group by question_id) a 
order by a.rate desc
limit 1

580. 统计各专业学生人数

一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。

写一个查询语句，查询 department 表中每个专业的学生人数（即使没有学生的专业也需列出）。

将你的查询结果按照学生人数降序排列。如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。

student 表格如下：

| Column Name | Type |
|--------------|-----------|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |
其中， student_id 是学生的学号， student_name 是学生的姓名， gender 是学生的性别， dept_id 是学生所属专业的专业编号。

department 表格如下：

| Column Name | Type |
|-------------|---------|
| dept_id | Integer |
| dept_name | String |
dept_id 是专业编号， dept_name 是专业名字。

这里是一个示例输入：
student 表格：

| student_id | student_name | gender | dept_id |
|------------|--------------|--------|---------|
| 1 | Jack | M | 1 |
| 2 | Jane | F | 1 |
| 3 | Mark | M | 2 |
department 表格：

| dept_id | dept_name |
|---------|-------------|
| 1 | Engineering |
| 2 | Science |
| 3 | Law |
示例输出为：

| dept_name | student_number |
|-------------|----------------|
| Engineering | 2 |
| Science | 1 |
| Law | 0 |

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/count-student-number-in-departments
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select * from 
(select a.dept_name,
       count(b.student_id) student_number 
from department a left join student b 
on a.dept_id = b.dept_id 
group by a.dept_name) c 
order by student_number desc,dept_name

585. 2016年的投资

写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

对于一个投保人，他在 2016 年成功投资的条件是：

他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。
输入格式:
表 insurance 格式如下：

| Column Name | Type |
|-------------|---------------|
| PID | INTEGER(11) |
| TIV_2015 | NUMERIC(15,2) |
| TIV_2016 | NUMERIC(15,2) |
| LAT | NUMERIC(5,2) |
| LON | NUMERIC(5,2) |
PID 字段是投保人的投保编号， TIV_2015 是该投保人在2015年的总投保金额， TIV_2016 是该投保人在2016年的投保金额， LAT 是投保人所在城市的维度， LON 是投保人所在城市的经度。

样例输入

| PID | TIV_2015 | TIV_2016 | LAT | LON |
|-----|----------|----------|-----|-----|
| 1 | 10 | 5 | 10 | 10 |
| 2 | 20 | 20 | 20 | 20 |
| 3 | 10 | 30 | 20 | 20 |
| 4 | 10 | 40 | 40 | 40 |
样例输出

| TIV_2016 |
|----------|
| 45.00 |
解释

就如最后一个投保人，第一个投保人同时满足两个条件：
1. 他在 2015 年的投保金额 TIV_2015 为 '10' ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。

第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。

所以返回的结果是第一个投保人和最后一个投保人的 TIV_2016 之和，结果是 45 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/investments-in-2016
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select round(sum(TIV_2016),2) TIV_2016 
from insurance a
where exists(select * from insurance b where a.pid <> b.pid and a.TIV_2015 = b.TIV_2015)
  and not exists(select * from insurance c where a.pid <> c.pid and a.LAT = c.LAT and a.LON = c.LON)

602. 好友申请 II ：谁有最多的好友

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。

表 request_accepted 存储了所有好友申请通过的数据记录，其中， requester_id 和 accepter_id 都是用户的编号。

| 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 |
写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。对于上面的样例数据，结果为：

| id | num |
|----|-----|
| 3 | 3 |
注意：

保证拥有最多好友数目的只有 1 个人。
好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

解释：

编号为 '3' 的人是编号为 '1'，'2' 和 '4' 的好友，所以他总共有 3 个好友，比其他人都多。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/friend-requests-ii-who-has-the-most-friends
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select * from 
(select id,count(*) num from 
(select requester_id id from request_accepted 
union all
select accepter_id id from request_accepted) a
group by id) b 
order by b.num desc
limit 1

608. 树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。
根：如果这个节点是整棵树的根，即没有父节点。
内部节点：如果这个节点既不是叶子节点也不是根节点。

写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：

+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+

解释

节点 '1' 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 '2' 和 '3' 。
节点 '2' 是内部节点，因为它有父节点 '1' ，也有孩子节点 '4' 和 '5' 。
节点 '3', '4' 和 '5' 都是叶子节点，因为它们都有父节点同时没有孩子节点。
样例中树的形态如下：

1
/ \
2 3
/ \
4 5

注意

如果树中只有一个节点，你只需要输出它的根属性

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/tree-node
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select * from 
(select distinct id,'Root' Type from tree where p_id is null 
union
select distinct id,'Inner' Type from tree a where a.p_id is not null and exists(select * from tree b where a.id = b.p_id)
union
select distinct id,'Leaf' Type from tree a where a.p_id is not null and not exists(select * from tree b where a.id = b.p_id)) c
order by c.id

612. 平面上的最近距离

表 point_2d 保存了所有点（多于 2 个点）的坐标 (x,y) ，这些点在平面上两两不重合。

写一个查询语句找到两点之间的最近距离，保留 2 位小数。

| x | y |
|----|----|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |

最近距离在点 (-1,-1) 和(-1,2) 之间，距离为 1.00 。所以输出应该为：

| shortest |
|----------|
| 1.00 |

注意：任意点之间的最远距离小于 10000 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/shortest-distance-in-a-plane
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select round(min(distance),2) shortest from 
(select sqrt((a.x-b.x)*(a.x-b.x)+(a.y-b.y)*(a.y-b.y)) distance
from point_2d a,point_2d b
where (a.x,a.y) <> (b.x,b.y)) c

614. 二级关注者

在 facebook 中，表 follow 会有 2 个字段： followee, follower ，分别表示被关注者和关注者。

请写一个 sql 查询语句，对每一个关注者，查询关注他的关注者的数目。

比方说：

+-------------+------------+
| followee | follower |
+-------------+------------+
| A | B |
| B | C |
| B | D |
| D | E |
+-------------+------------+
应该输出：

+-------------+------------+
| follower | num |
+-------------+------------+
| B | 2 |
| D | 1 |
+-------------+------------+
解释：

B 和 D 都在在 follower 字段中出现，作为被关注者，B 被 C 和 D 关注，D 被 E 关注。A 不在 follower 字段内，所以A不在输出列表中。

注意：

被关注者永远不会被他 / 她自己关注。
将结果按照字典序返回。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/second-degree-follower
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.follower,count(distinct b.follower) num
from follow a,follow b
where a.follower = b.followee   
group by a.follower 
order by a.follower

626. 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：

+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/exchange-seats
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select * from 
(select id+1 id,student from seat 
where id % 2 = 1 
  and id <> (select max(id) from seat) 
union
select id-1 id,student from seat 
where id % 2 = 0
union
select id,student from seat 
where id % 2 = 1
  and id = (select max(id) from seat)) a
order by a.id

1045. 买下所有产品的客户

Customer 表：

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| customer_id | int |
| product_key | int |
+-------------+---------+
product_key 是 Customer 表的外键。
Product 表：

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_key | int |
+-------------+---------+
product_key 是这张表的主键。

写一条 SQL 查询语句，从 Customer 表中查询购买了 Product 表中所有产品的客户的 id。

示例：

Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1 | 5 |
| 2 | 6 |
| 3 | 5 |
| 3 | 6 |
| 1 | 6 |
+-------------+-------------+

Product 表：
+-------------+
| product_key |
+-------------+
| 5 |
| 6 |
+-------------+

Result 表：
+-------------+
| customer_id |
+-------------+
| 1 |
| 3 |
+-------------+
购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customers-who-bought-all-products
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select customer_id from Customer group by customer_id having count(distinct product_key) = (select count(*) from Product)

1070. 产品销售分析 III

销售表 Sales：

+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
sale_id 是此表的主键。
product_id 是产品表的外键。
请注意，价格是按每单位计的。
产品表 Product：

编写一个 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 | first_year | quantity | price |
+------------+------------+----------+-------+
| 100 | 2008 | 10 | 5000 |
| 200 | 2011 | 15 | 9000 |
+------------+------------+----------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-sales-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.product_id,a.year first_year,a.quantity,a.price  
from Sales a where (a.product_id,a.year) in (select b.product_id,min(b.year) from Sales b group by b.product_id )

1077. 项目员工 III

项目表 Project：

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
(project_id, employee_id) 是这个表的主键
employee_id 是员工表 Employee 的外键
员工表 Employee：

写一个 SQL 查询语句，报告在每一个项目中经验最丰富的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

查询结果格式在以下示例中：

Project 表：
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+

Employee 表：
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 3 |
| 4 | Doe | 2 |
+-------------+--------+------------------+

Result 表：
+-------------+---------------+
| project_id | employee_id |
+-------------+---------------+
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
+-------------+---------------+
employee_id 为 1 和 3 的员工在 project_id 为 1 的项目中拥有最丰富的经验。在 project_id 为 2 的项目中，employee_id 为 1 的员工拥有最丰富的经验。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/project-employees-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select project_id,employee_id from 
(select a.*,b.experience_years,
       dense_rank() over(partition by a.project_id order by b.experience_years desc) cnt
from Project a,Employee b 
where a.employee_id = b.employee_id) c 
where c.cnt = 1

1098. 小众书籍

书籍表 Books：

你需要写一段 SQL 命令，筛选出过去一年中订单总量少于10本的书籍。

注意：不考虑上架（available from）距今不满一个月的书籍。并且假设今天是 2019-06-23 。

下面是样例输出结果：

Books 表：
+---------+--------------------+----------------+
| book_id | name | available_from |
+---------+--------------------+----------------+
| 1 | "Kalila And Demna" | 2010-01-01 |
| 2 | "28 Letters" | 2012-05-12 |
| 3 | "The Hobbit" | 2019-06-10 |
| 4 | "13 Reasons Why" | 2019-06-01 |
| 5 | "The Hunger Games" | 2008-09-21 |
+---------+--------------------+----------------+

Orders 表：
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1 | 1 | 2 | 2018-07-26 |
| 2 | 1 | 1 | 2018-11-05 |
| 3 | 3 | 8 | 2019-06-11 |
| 4 | 4 | 6 | 2019-06-05 |
| 5 | 4 | 5 | 2019-06-20 |
| 6 | 5 | 9 | 2009-02-02 |
| 7 | 5 | 8 | 2010-04-13 |
+----------+---------+----------+---------------+

Result 表：
+-----------+--------------------+
| book_id | name |
+-----------+--------------------+
| 1 | "Kalila And Demna" |
| 2 | "28 Letters" |
| 5 | "The Hunger Games" |
+-----------+--------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/unpopular-books
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.book_id,a.name
from Books a
where a.available_from <= '2019-05-23'
  and not exists(select * from Orders b where a.book_id = b.book_id and b.dispatch_date between '2018-06-23' and '2019-06-23' group by b.book_id having sum(quantity) >= 10)

1107. 每日新用户统计

Traffic 表：

编写一个 SQL 查询，以查询从今天起最多 90 天内，每个日期该日期首次登录的用户数。假设今天是 2019-06-30.

查询结果格式如下例所示：

Traffic 表：
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1 | login | 2019-05-01 |
| 1 | homepage | 2019-05-01 |
| 1 | logout | 2019-05-01 |
| 2 | login | 2019-06-21 |
| 2 | logout | 2019-06-21 |
| 3 | login | 2019-01-01 |
| 3 | jobs | 2019-01-01 |
| 3 | logout | 2019-01-01 |
| 4 | login | 2019-06-21 |
| 4 | groups | 2019-06-21 |
| 4 | logout | 2019-06-21 |
| 5 | login | 2019-03-01 |
| 5 | logout | 2019-03-01 |
| 5 | login | 2019-06-21 |
| 5 | logout | 2019-06-21 |
+---------+----------+---------------+

Result 表：
+------------+-------------+
| login_date | user_count |
+------------+-------------+
| 2019-05-01 | 1 |
| 2019-06-21 | 2 |
+------------+-------------+
请注意，我们只关心用户数非零的日期.
ID 为 5 的用户第一次登陆于 2019-03-01，因此他不算在 2019-06-21 的的统计内。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/new-users-daily-count
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select activity_date login_date,count(user_id) user_count  
from
(select user_id,min(activity_date) activity_date from Traffic where activity = 'login' group by user_id) a 
where datediff('2019-06-30',activity_date) <= 90
group by activity_date

1112. 每位学生的最高成绩

表：Enrollments

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| student_id | int |
| course_id | int |
| grade | int |
+---------------+---------+
(student_id, course_id) 是该表的主键。

编写一个 SQL 查询，查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

查询结果格式如下所示：

Enrollments 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 2 | 2 | 95 |
| 2 | 3 | 95 |
| 1 | 1 | 90 |
| 1 | 2 | 99 |
| 3 | 1 | 80 |
| 3 | 2 | 75 |
| 3 | 3 | 82 |
+------------+-----------+-------+

Result 表：
+------------+-------------------+
| student_id | course_id | grade |
+------------+-----------+-------+
| 1 | 2 | 99 |
| 2 | 2 | 95 |
| 3 | 3 | 82 |
+------------+-----------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/highest-grade-for-each-student
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select student_id,course_id,grade from 
(select a.*,
       rank() over(partition by a.student_id order by grade desc,course_id) cnt
from Enrollments a) b
where b.cnt = 1
order by b.student_id

1126. 查询活跃业务

事件表：Events

写一段 SQL 来查询所有活跃的业务。

如果一个业务的某个事件类型的发生次数大于此事件类型在所有业务中的平均发生次数，并且该业务至少有两个这样的事件类型，那么该业务就可被看做是活跃业务。

查询结果格式如下所示：

Events table:
+-------------+------------+------------+
| business_id | event_type | occurences |
+-------------+------------+------------+
| 1 | reviews | 7 |
| 3 | reviews | 3 |
| 1 | ads | 11 |
| 2 | ads | 7 |
| 3 | ads | 6 |
| 1 | page views | 3 |
| 2 | page views | 12 |
+-------------+------------+------------+

结果表
+-------------+
| business_id |
+-------------+
| 1 |
+-------------+
'reviews'、 'ads' 和 'page views' 的总平均发生次数分别是 (7+3)/2=5, (11+7+6)/3=8, (3+12)/2=7.5。
id 为 1 的业务有 7 个 'reviews' 事件（大于 5）和 11 个 'ads' 事件（大于 8），所以它是活跃业务。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/active-businesses
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select c.business_id from 
(select a.*,
       case when a.occurences > b.cnt then 1 else 0 end flag
from Events a,
(select event_type,avg(occurences) cnt from Events group by event_type) b
where a.event_type = b.event_type) c
group by c.business_id 
having sum(flag) >= 2

1132. 报告的记录 II

动作表： Actions

编写一段 SQL 来查找：在被报告为垃圾广告的帖子中，被移除的帖子的每日平均占比，四舍五入到小数点后 2 位。

查询结果的格式如下：

Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra |
+---------+---------+-------------+--------+--------+
| 1 | 1 | 2019-07-01 | view | null |
| 1 | 1 | 2019-07-01 | like | null |
| 1 | 1 | 2019-07-01 | share | null |
| 2 | 2 | 2019-07-04 | view | null |
| 2 | 2 | 2019-07-04 | report | spam |
| 3 | 4 | 2019-07-04 | view | null |
| 3 | 4 | 2019-07-04 | report | spam |
| 4 | 3 | 2019-07-02 | view | null |
| 4 | 3 | 2019-07-02 | report | spam |
| 5 | 2 | 2019-07-03 | view | null |
| 5 | 2 | 2019-07-03 | report | racism |
| 5 | 5 | 2019-07-03 | view | null |
| 5 | 5 | 2019-07-03 | report | racism |
+---------+---------+-------------+--------+--------+

Removals table:
+---------+-------------+
| post_id | remove_date |
+---------+-------------+
| 2 | 2019-07-20 |
| 3 | 2019-07-18 |
+---------+-------------+

Result table:
+-----------------------+
| average_daily_percent |
+-----------------------+
| 75.00 |
+-----------------------+
2019-07-04 的垃圾广告移除率是 50%，因为有两张帖子被报告为垃圾广告，但只有一个得到移除。
2019-07-02 的垃圾广告移除率是 100%，因为有一张帖子被举报为垃圾广告并得到移除。
其余几天没有收到垃圾广告的举报，因此平均值为：(50 + 100) / 2 = 75%
注意，输出仅需要一个平均值即可，我们并不关注移除操作的日期。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/reported-posts-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select round(sum(rate)/count(*),2) average_daily_percent from 
(select a.action_date,
       count(distinct b.post_id)/count(distinct a.post_id)*100 rate
from 
(select distinct * from Actions where action = 'report' and extra = 'spam') a left join Removals b 
on a.post_id = b.post_id
group by a.action_date) c

1149. 文章浏览 II

Table: Views

编写一条 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 |
+------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/article-views-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct a.viewer_id id from 
(select viewer_id,view_date,
       count(distinct article_id) cnt
from Views 
group by viewer_id,view_date) a 
where a.cnt > 1
order by a.viewer_id

1158. 市场分析 I

Table: Users

请写出一条SQL语句以查询每个用户的注册日期和在 2019 年作为买家的订单总数。

查询结果格式如下：

Users table:
+---------+------------+----------------+
| user_id | join_date | favorite_brand |
+---------+------------+----------------+
| 1 | 2018-01-01 | Lenovo |
| 2 | 2018-02-09 | Samsung |
| 3 | 2018-01-19 | LG |
| 4 | 2018-05-21 | HP |
+---------+------------+----------------+

Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1 | 2019-08-01 | 4 | 1 | 2 |
| 2 | 2018-08-02 | 2 | 1 | 3 |
| 3 | 2019-08-03 | 3 | 2 | 3 |
| 4 | 2018-08-04 | 1 | 4 | 2 |
| 5 | 2018-08-04 | 1 | 3 | 4 |
| 6 | 2019-08-05 | 2 | 2 | 4 |
+----------+------------+---------+----------+-----------+

Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1 | Samsung |
| 2 | Lenovo |
| 3 | LG |
| 4 | HP |
+---------+------------+

Result table:
+-----------+------------+----------------+
| buyer_id | join_date | orders_in_2019 |
+-----------+------------+----------------+
| 1 | 2018-01-01 | 1 |
| 2 | 2018-02-09 | 2 |
| 3 | 2018-01-19 | 0 |
| 4 | 2018-05-21 | 0 |
+-----------+------------+----------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/market-analysis-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.user_id buyer_id,a.join_date,coalesce(b.cnt,0) orders_in_2019  
from Users a left join (select buyer_id,count(*) cnt from Orders where order_date between '2019-01-01' and '2019-12-31' group by buyer_id) b 
on a.user_id = b.buyer_id

1164. 指定日期的产品价格

产品数据表: Products

写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10。

查询结果格式如下例所示：

Products table:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
+------------+-----------+-------------+

Result table:
+------------+-------+
| product_id | price |
+------------+-------+
| 2 | 50 |
| 1 | 35 |
| 3 | 10 |
+------------+-------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/product-price-at-a-given-date
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select c.product_id,coalesce(b.price,10) price 
from 
(select distinct product_id from Products) c left join
(select a.product_id,a.new_price price from Products a where (a.product_id,a.change_date) in
(select product_id,max(change_date) change_date from Products where change_date <= '2019-08-16' group by product_id)) b
on c.product_id = b.product_id

1174. 即时食物配送 II

配送表: Delivery

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

查询结果如下所示：

Delivery 表：
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-12 |
| 4 | 3 | 2019-08-24 | 2019-08-24 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
+-------------+-------------+------------+-----------------------------+

Result 表：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00 |
+----------------------+
1 号顾客的 1 号订单是首次订单，并且是计划订单。
2 号顾客的 2 号订单是首次订单，并且是即时订单。
3 号顾客的 5 号订单是首次订单，并且是计划订单。
4 号顾客的 7 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/immediate-food-delivery-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select round(sum(case when is_now = 1 and is_first = 1 then 1 else 0 end)/sum(is_first) * 100,2)immediate_percentage from 
(select a.*,b.first_date,
       case when order_date = first_date then 1 else 0 end is_first,
       case when order_date = customer_pref_delivery_date then 1 else 0 end is_now
from Delivery a,
(select customer_id,min(order_date) first_date from Delivery group by customer_id) b
where a.customer_id = b.customer_id) c

1193. 每月交易 I

Table: Transactions

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

查询结果格式如下所示：

Transactions table:
+------+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+------+---------+----------+--------+------------+
| 121 | US | approved | 1000 | 2018-12-18 |
| 122 | US | declined | 2000 | 2018-12-19 |
| 123 | US | approved | 2000 | 2019-01-01 |
| 124 | DE | approved | 2000 | 2019-01-07 |
+------+---------+----------+--------+------------+

Result table:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12 | US | 2 | 1 | 3000 | 1000 |
| 2019-01 | US | 1 | 1 | 2000 | 2000 |
| 2019-01 | DE | 1 | 1 | 2000 | 2000 |
+----------+---------+-------------+----------------+--------------------+-----------------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/monthly-transactions-i
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select left(trans_date,7) month,country,
       count(*) trans_count,
       count(case when state = 'approved' then state end) approved_count,
       sum(amount) trans_total_amount,
       coalesce(sum(case when state = 'approved' then amount end),0) approved_total_amount 
from Transactions 
group by left(trans_date,7),country

1204. 最后一个能进入电梯的人

表: Queue

电梯最大载重量为 1000。

写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯。

查询结果如下所示 :

Queue 表
+-----------+-------------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------------+--------+------+
| 5 | George Washington | 250 | 1 |
| 3 | John Adams | 350 | 2 |
| 6 | Thomas Jefferson | 400 | 3 |
| 2 | Will Johnliams | 200 | 4 |
| 4 | Thomas Jefferson | 175 | 5 |
| 1 | James Elephant | 500 | 6 |
+-----------+-------------------+--------+------+

Result 表
+-------------------+
| person_name |
+-------------------+
| Thomas Jefferson |
+-------------------+

为了简化，Queue 表按 turn 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 将可以进入电梯,因为他们的体重和为 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一个体重合适并进入电梯的人。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/last-person-to-fit-in-the-bus
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select person_name from Queue where turn in
(select max(turn) from 
(select a.*,
       sum(weight) over(order by turn) cnt
from Queue a) b 
where b.cnt <= 1000)

1205. 每月交易II

Transactions 记录表

编写一个 SQL 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。

注意：在您的查询中，只需显示给定月份和国家，忽略所有为零的行。

查询结果格式如下所示：

Transactions 表：
+-----+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+-----+---------+----------+--------+------------+
| 101 | US | approved | 1000 | 2019-05-18 |
| 102 | US | declined | 2000 | 2019-05-19 |
| 103 | US | approved | 3000 | 2019-06-10 |
| 104 | US | declined | 4000 | 2019-06-13 |
| 105 | US | approved | 5000 | 2019-06-15 |
+-----+---------+----------+--------+------------+

Chargebacks 表：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102 | 2019-05-29 |
| 101 | 2019-06-30 |
| 105 | 2019-09-18 |
+----------+------------+

Result 表：
+---------+---------+----------------+-----------------+------------------+-------------------+
| month | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+---------+---------+----------------+-----------------+------------------+-------------------+
| 2019-05 | US | 1 | 1000 | 1 | 2000 |
| 2019-06 | US | 2 | 8000 | 1 | 1000 |
| 2019-09 | US | 0 | 0 | 1 | 5000 |
+---------+---------+----------------+-----------------+------------------+-------------------

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/monthly-transactions-ii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with tmp1 as
(select left(trans_date,7) month,country,
       count(*) approved_count,
       sum(amount) approved_amount 
from Transactions 
where state = 'approved'
group by left(trans_date,7),country)
,tmp2 as
(select left(b.trans_date,7) month,a.country,
       count(*) chargeback_count,
       sum(amount) chargeback_amount 
from Transactions a,Chargebacks b 
where a.id = b.trans_id 
group by left(b.trans_date,7),a.country)

select a.month,a.country,a.approved_count,a.approved_amount,coalesce(b.chargeback_count,0) chargeback_count,coalesce(b.chargeback_amount,0) chargeback_amount
from tmp1 a left join tmp2 b 
on a.month = b.month
and a.country = b.country
union
select b.month,b.country,coalesce(a.approved_count,0) approved_count,coalesce(a.approved_amount,0) approved_amount,coalesce(b.chargeback_count,0) chargeback_count,coalesce(b.chargeback_amount,0) chargeback_amount
from tmp1 a right join tmp2 b 
on a.month = b.month
and a.country = b.country

1212. 查询球队积分

Table: Teams

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| match_id | int |
| host_team | int |
| guest_team | int |
| host_goals | int |
| guest_goals | int |
+---------------+---------+
此表的主键是 match_id，表中的每一行都代表一场已结束的比赛，比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。

积分规则如下：

赢一场得三分；
平一场得一分；
输一场不得分。
写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

查询结果格式如下：

Teams table:
+-----------+--------------+
| team_id | team_name |
+-----------+--------------+
| 10 | Leetcode FC |
| 20 | NewYork FC |
| 30 | Atlanta FC |
| 40 | Chicago FC |
| 50 | Toronto FC |
+-----------+--------------+

Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id | host_team | guest_team | host_goals | guest_goals |
+------------+--------------+---------------+-------------+--------------+
| 1 | 10 | 20 | 3 | 0 |
| 2 | 30 | 10 | 2 | 2 |
| 3 | 10 | 50 | 5 | 1 |
| 4 | 20 | 30 | 1 | 0 |
| 5 | 50 | 30 | 1 | 0 |
+------------+--------------+---------------+-------------+--------------+

Result table:
+------------+--------------+---------------+
| team_id | team_name | num_points |
+------------+--------------+---------------+
| 10 | Leetcode FC | 7 |
| 20 | NewYork FC | 3 |
| 50 | Toronto FC | 3 |
| 30 | Atlanta FC | 1 |
| 40 | Chicago FC | 0 |
+------------+--------------+---------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/team-scores-in-football-tournament
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select d.team_id,d.team_name,coalesce(d.points_a,0)+coalesce(d.points_b,0) num_points         
from 
(select a.*,b.num_points points_a,c.num_points points_b 
from Teams a left join
(select host_team,
       sum(case when host_goals > guest_goals then 3 
                when host_goals = guest_goals then 1 else 0 end) num_points    
from Matches 
group by host_team) b   
on team_id = host_team
left join
(select guest_team,
       sum(case when guest_goals > host_goals then 3 
                when guest_goals = host_goals then 1 else 0 end) num_points    
from Matches 
group by guest_team) c 
on team_id = guest_team) d
order by num_points desc,d.team_id

1264. 页面推荐

朋友关系列表： Friendship

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user1_id | int |
| user2_id | int |
+---------------+---------+
这张表的主键是 (user1_id, user2_id)。
这张表的每一行代表着 user1_id 和 user2_id 之间存在着朋友关系。

喜欢列表： Likes

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| page_id | int |
+-------------+---------+
这张表的主键是 (user_id, page_id)。
这张表的每一行代表着 user_id 喜欢 page_id。

写一段 SQL 向user_id = 1 的用户，推荐其朋友们喜欢的页面。不要推荐该用户已经喜欢的页面。

你返回的结果中不应当包含重复项。

返回结果的格式如下例所示：

Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 6 | 1 |
+----------+----------+

Likes table:
+---------+---------+
| user_id | page_id |
+---------+---------+
| 1 | 88 |
| 2 | 23 |
| 3 | 24 |
| 4 | 56 |
| 5 | 11 |
| 6 | 33 |
| 2 | 77 |
| 3 | 77 |
| 6 | 88 |
+---------+---------+

Result table:
+------------------+
| recommended_page |
+------------------+
| 23 |
| 24 |
| 56 |
| 33 |
| 77 |
+------------------+
用户1 同用户2, 3, 4, 6 是朋友关系。
推荐页面为：页面23 来自于用户2, 页面24 来自于用户3, 页面56 来自于用户3 以及页面33 来自于用户6。
页面77 同时被用户2 和用户3 推荐。
页面88 没有被推荐，因为用户1 已经喜欢了它。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/page-recommendations
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct page_id recommended_page from Likes a where exists(select * from Friendship b where (a.user_id = b.user2_id and b.user1_id = 1) or (a.user_id = b.user1_id and b.user2_id = 1)) and a.page_id not in (select page_id from Likes where user_id = 1)

1270. 向公司CEO汇报工作的所有人

员工表：Employees

用 SQL 查询出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。

由于公司规模较小，经理之间的间接关系不超过 3 个经理。

可以以任何顺序返回的结果，不需要去重。

查询结果示例如下：

Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1 | Boss | 1 |
| 3 | Alice | 3 |
| 2 | Bob | 1 |
| 4 | Daniel | 2 |
| 7 | Luis | 4 |
| 8 | Jhon | 3 |
| 9 | Angela | 8 |
| 77 | Robert | 1 |
+-------------+---------------+------------+

Result table:
+-------------+
| employee_id |
+-------------+
| 2 |
| 77 |
| 4 |
| 7 |
+-------------+

公司 CEO 的 employee_id 是 1.
employee_id 是 2 和 77 的职员直接汇报给公司 CEO。
employee_id 是 4 的职员间接汇报给公司 CEO 4 --> 2 --> 1 。
employee_id 是 7 的职员间接汇报给公司 CEO 7 --> 4 --> 2 --> 1 。
employee_id 是 3, 8 ，9 的职员不会直接或间接的汇报给公司 CEO。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/all-people-report-to-the-given-manager
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select employee_id from Employees where manager_id in (select employee_id from Employees where employee_id = 1) and employee_id <> 1
union 
select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id in (select employee_id from Employees where employee_id = 1) and employee_id <> 1)
union
select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id in (select employee_id from Employees where manager_id in (select employee_id from Employees where employee_id = 1) and employee_id <> 1))

1285. 找到连续区间的开始和结束数字

表：Logs

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| log_id | int |
+---------------+---------+
id 是上表的主键。
上表的每一行包含日志表中的一个 ID。

后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

将查询表按照 start_id 排序。

查询结果格式如下面的例子：

Logs 表：
+------------+
| log_id |
+------------+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 10 |
+------------+

结果表：
+------------+--------------+
| start_id | end_id |
+------------+--------------+
| 1 | 3 |
| 7 | 8 |
| 10 | 10 |
+------------+--------------+
结果表应包含 Logs 表中的所有区间。
从 1 到 3 在表中。
从 4 到 6 不在表中。
从 7 到 8 在表中。
9 不在表中。
10 在表中。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-the-start-and-end-number-of-continuous-ranges
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select min(log_id) start_id,max(log_id) end_id 
from
(select distinct log_id,
       log_id-row_number() over(order by log_id) rn
from Logs) a
group by a.rn

1308. 不同性别每日分数总计

表: Scores

写一条SQL语句查询每种性别在每一天的总分，并按性别和日期对查询结果排序

下面是查询结果格式的例子：

Scores表:
+-------------+--------+------------+--------------+
| player_name | gender | day | score_points |
+-------------+--------+------------+--------------+
| Aron | F | 2020-01-01 | 17 |
| Alice | F | 2020-01-07 | 23 |
| Bajrang | M | 2020-01-07 | 7 |
| Khali | M | 2019-12-25 | 11 |
| Slaman | M | 2019-12-30 | 13 |
| Joe | M | 2019-12-31 | 3 |
| Jose | M | 2019-12-18 | 2 |
| Priya | F | 2019-12-31 | 23 |
| Priyanka | F | 2019-12-30 | 17 |
+-------------+--------+------------+--------------+
结果表:
+--------+------------+-------+
| gender | day | total |
+--------+------------+-------+
| F | 2019-12-30 | 17 |
| F | 2019-12-31 | 40 |
| F | 2020-01-01 | 57 |
| F | 2020-01-07 | 80 |
| M | 2019-12-18 | 2 |
| M | 2019-12-25 | 13 |
| M | 2019-12-30 | 26 |
| M | 2019-12-31 | 29 |
| M | 2020-01-07 | 36 |
+--------+------------+-------+
女性队伍:
第一天是 2019-12-30，Priyanka 获得 17 分，队伍的总分是 17 分
第二天是 2019-12-31, Priya 获得 23 分，队伍的总分是 40 分
第三天是 2020-01-01, Aron 获得 17 分，队伍的总分是 57 分
第四天是 2020-01-07, Alice 获得 23 分，队伍的总分是 80 分
男性队伍：
第一天是 2019-12-18, Jose 获得 2 分，队伍的总分是 2 分
第二天是 2019-12-25, Khali 获得 11 分，队伍的总分是 13 分
第三天是 2019-12-30, Slaman 获得 13 分，队伍的总分是 26 分
第四天是 2019-12-31, Joe 获得 3 分，队伍的总分是 29 分
第五天是 2020-01-07, Bajrang 获得 7 分，队伍的总分是 36 分

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/running-total-for-different-genders
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select gender,day,sum(score_points) over(partition by gender order by day) total from Scores
order by gender,day

1321. 餐馆营业额变化增长

表: Customer

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值

查询结果格式的例子如下：

查询结果按 visited_on 排序
average_amount 要保留两位小数，日期数据的格式为 ('YYYY-MM-DD')

Customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name | visited_on | amount |
+-------------+--------------+--------------+-------------+
| 1 | Jhon | 2019-01-01 | 100 |
| 2 | Daniel | 2019-01-02 | 110 |
| 3 | Jade | 2019-01-03 | 120 |
| 4 | Khaled | 2019-01-04 | 130 |
| 5 | Winston | 2019-01-05 | 110 |
| 6 | Elvis | 2019-01-06 | 140 |
| 7 | Anna | 2019-01-07 | 150 |
| 8 | Maria | 2019-01-08 | 80 |
| 9 | Jaze | 2019-01-09 | 110 |
| 1 | Jhon | 2019-01-10 | 130 |
| 3 | Jade | 2019-01-10 | 150 |
+-------------+--------------+--------------+-------------+

结果表:
+--------------+--------------+----------------+
| visited_on | amount | average_amount |
+--------------+--------------+----------------+
| 2019-01-07 | 860 | 122.86 |
| 2019-01-08 | 840 | 120 |
| 2019-01-09 | 840 | 120 |
| 2019-01-10 | 1000 | 142.86 |
+--------------+--------------+----------------+

第一个七天消费平均值从 2019-01-01 到 2019-01-07 是 (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/restaurant-growth
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.visited_on,
       sum(b.amount) amount,
       round(avg(b.amount),2) average_amount
from 
(select visited_on,sum(amount) amount from Customer group by visited_on) a, 
(select visited_on,sum(amount) amount from Customer group by visited_on) b 
where datediff(a.visited_on,b.visited_on) <= 6
  and datediff(a.visited_on,b.visited_on) >= 0
  and datediff(a.visited_on,(select min(visited_on) from Customer)) >=6
group by a.visited_on

341. 电影评分

表：Movies

请你编写一组 SQL 查询：

查找评论电影数量最多的用户名。
如果出现平局，返回字典序较小的用户名。

查找在 2020 年 2 月平均评分最高的电影名称。
如果出现平局，返回字典序较小的电影名称。

查询分两行返回，查询结果格式如下例所示：

Movies 表：
+-------------+--------------+
| movie_id | title |
+-------------+--------------+
| 1 | Avengers |
| 2 | Frozen 2 |
| 3 | Joker |
+-------------+--------------+

Users 表：
+-------------+--------------+
| user_id | name |
+-------------+--------------+
| 1 | Daniel |
| 2 | Monica |
| 3 | Maria |
| 4 | James |
+-------------+--------------+

Movie_Rating 表：
+-------------+--------------+--------------+-------------+
| 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 |
+-------------+--------------+--------------+-------------+

Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"）但是 Daniel 字典序比较小。
Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/movie-rating
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select * from 
(select name results from 
(select name,count(*) cnt 
from Movie_Rating a,Users b 
where a.user_id = b.user_id
group by name              
order by cnt desc,b.name) c
limit 1) d
union
select e.title from 
(select b.title,avg(a.rating) cnt
from Movie_Rating a,Movies b
where a.movie_id = b.movie_id
  and left(a.created_at,7) = '2020-02'
group by b.title       
order by cnt desc,b.title       
limit 1) e

1355. 活动参与者

表: Friends

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表的主键
name 是活动的名字

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字

可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表

注意：名称相同 id 不同的参与者算作两个人

下面是查询结果格式的例子：

Activities 表:
+------+--------------+
| id | name |
+------+--------------+
| 1 | Eating |
| 2 | Singing |
| 3 | Horse Riding |
+------+--------------+

Result 表:
+--------------+
| activity |
+--------------+
| Singing |
+--------------+

Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)
Singing 活动有两个人参加 (Victor J. and Jade W.)

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/activity-participants
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct b.activity from 
(select a.activity,
       dense_rank() over(order by cnt desc) cnt1,
       dense_rank() over(order by cnt) cnt2
from 
(select activity,count(*) cnt from Friends group by activity) a) b
where cnt1 <> 1
  and cnt2 <> 1

1364. 顾客的可信联系人数量

顾客表：Customers

联系方式表：Contacts

发票表：Invoices

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| invoice_id | int |
| price | int |
| user_id | int |
+--------------+---------+
invoice_id 是这张表的主键。
此表的每一行分别表示编号为 user_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。

为每张发票 invoice_id 编写一个SQL查询以查找以下内容：

customer_name：与发票相关的顾客名称。
price：发票的价格。
contacts_cnt：该顾客的联系人数量。
trusted_contacts_cnt：可信联系人的数量：既是该顾客的联系人又是商店顾客的联系人数量（即：可信联系人的电子邮件存在于客户表中）。
将查询的结果按照 invoice_id 排序。

查询结果的格式如下例所示：

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 |
+------------+-------+---------+
Result table:
+------------+---------------+-------+--------------+----------------------+
| 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 |
+------------+---------------+-------+--------------+----------------------+
Alice 有三位联系人，其中两位(Bob 和 John)是可信联系人。
Bob 有两位联系人, 他们中的任何一位都不是可信联系人。
Alex 只有一位联系人(Alice)，并是一位可信联系人。
John 没有任何联系人。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/number-of-trusted-contacts-of-a-customer
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select f.invoice_id,f.customer_name,f.price,coalesce(g.cnt1,0) contacts_cnt ,coalesce(g.cnt2,0) trusted_contacts_cnt   
from 
(select a.*,b.customer_name 
from Invoices a,Customers b 
where a.user_id = b.customer_id) f 
left join  
(select e.customer_id,count(e.contact_name) cnt1,count(e.contact_nameb) cnt2  
from 
(select c.*,d.customer_name contact_nameb 
from 
(select a.*,b.contact_name  
from Customers a,Contacts b 
where a.customer_id = b.user_id) c left join Customers d
on c.contact_name = d.customer_name) e
group by e.customer_id) g 
on f.user_id = g.customer_id 
order by f.invoice_id

1393. 股票的资本损益

Stocks 表：

编写一个SQL查询来报告每支股票的资本损益。

股票的资本损益是一次或多次买卖股票后的全部收益或损失。

以任意顺序返回结果即可。

SQL查询结果的格式如下例所示：

Stocks 表:
+---------------+-----------+---------------+--------+
| stock_name | operation | operation_day | price |
+---------------+-----------+---------------+--------+
| Leetcode | Buy | 1 | 1000 |
| Corona Masks | Buy | 2 | 10 |
| Leetcode | Sell | 5 | 9000 |
| Handbags | Buy | 17 | 30000 |
| Corona Masks | Sell | 3 | 1010 |
| Corona Masks | Buy | 4 | 1000 |
| Corona Masks | Sell | 5 | 500 |
| Corona Masks | Buy | 6 | 1000 |
| Handbags | Sell | 29 | 7000 |
| Corona Masks | Sell | 10 | 10000 |
+---------------+-----------+---------------+--------+

Result 表:
+---------------+-------------------+
| stock_name | capital_gain_loss |
+---------------+-------------------+
| Corona Masks | 9500 |
| Leetcode | 8000 |
| Handbags | -23000 |
+---------------+-------------------+
Leetcode 股票在第一天以1000美元的价格买入，在第五天以9000美元的价格卖出。资本收益=9000-1000=8000美元。
Handbags 股票在第17天以30000美元的价格买入，在第29天以7000美元的价格卖出。资本损失=7000-30000=-23000美元。
Corona Masks 股票在第1天以10美元的价格买入，在第3天以1010美元的价格卖出。在第4天以1000美元的价格再次购买，在第5天以500美元的价格出售。最后，它在第6天以1000美元的价格被买走，在第10天以10000美元的价格被卖掉。资本损益是每次（’Buy'->'Sell'）操作资本收益或损失的和=（1010-10）+（500-1000）+（10000-1000）=1000-500+9000=9500美元。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/capital-gainloss
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select stock_name,
       sum(case when operation = 'Sell' then price end) - sum(case when operation = 'Buy' then price end) capital_gain_loss 
from Stocks 
group by stock_name

1398. 购买了产品 A 和产品 B 却没有购买产品 C 的顾客

Customers 表：

Orders 表：

请你设计 SQL 查询来报告购买了产品 A 和产品 B 却没有购买产品 C 的顾客的 ID 和姓名（ customer_id 和 customer_name ），我们将基于此结果为他们推荐产品 C 。
您返回的查询结果需要按照 customer_id 排序。

查询结果如下例所示。

Customers table:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | Daniel |
| 2 | Diana |
| 3 | Elizabeth |
| 4 | Jhon |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id | customer_id | product_name |
+------------+--------------+---------------+
| 10 | 1 | A |
| 20 | 1 | B |
| 30 | 1 | D |
| 40 | 1 | C |
| 50 | 2 | A |
| 60 | 3 | A |
| 70 | 3 | B |
| 80 | 3 | D |
| 90 | 4 | C |
+------------+--------------+---------------+

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/customers-who-bought-products-a-and-b-but-not-c
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct a.customer_id,b.customer_name 
from Orders a,Customers b 
where a.product_name = 'A'
  and exists(select * from Orders b where a.customer_id = b.customer_id and b.product_name = 'B')
  and not exists(select * from Orders b where a.customer_id = b.customer_id and b.product_name = 'C')
  and a.customer_id = b.customer_id

1421. 净现值查询

表: NPV

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
| npv | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一笔存货的年份, id 和对应净现值的信息.

表: Queries

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| year | int |
+---------------+---------+
(id, year) 是该表主键.
该表有每一次查询所对应存货的 id 和年份的信息.

写一个 SQL, 找到 Queries 表中每一次查询的净现值.

结果表没有顺序要求.

查询结果的格式如下所示:

NPV 表:
+------+--------+--------+
| id | year | npv |
+------+--------+--------+
| 1 | 2018 | 100 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 11 | 2020 | 99 |
| 7 | 2019 | 0 |
+------+--------+--------+

Queries 表:
+------+--------+
| id | year |
+------+--------+
| 1 | 2019 |
| 2 | 2008 |
| 3 | 2009 |
| 7 | 2018 |
| 7 | 2019 |
| 7 | 2020 |
| 13 | 2019 |
+------+--------+

结果表:
+------+--------+--------+
| id | year | npv |
+------+--------+--------+
| 1 | 2019 | 113 |
| 2 | 2008 | 121 |
| 3 | 2009 | 12 |
| 7 | 2018 | 0 |
| 7 | 2019 | 0 |
| 7 | 2020 | 30 |
| 13 | 2019 | 40 |
+------+--------+--------+

(7, 2018)的净现值不在 NPV 表中, 我们把它看作是 0.
所有其它查询的净现值都能在 NPV 表中找到.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/npv-queries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.*,coalesce(b.npv,0) npv    
from Queries a left join NPV b 
on a.id = b.id 
and a.year = b.year

1440. 计算布尔表达式的值

表 Variables:

表 Expressions:

写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式.

返回的结果表没有顺序要求.

查询结果格式如下例所示.

Variables 表:
+------+-------+
| name | value |
+------+-------+
| x | 66 |
| y | 77 |
+------+-------+

Expressions 表:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x | > | y |
| x | < | y |
| x | = | y |
| y | > | x |
| y | < | x |
| x | = | x |
+--------------+----------+---------------+

Result 表:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x | > | y | false |
| x | < | y | true |
| x | = | y | false |
| y | > | x | true |
| y | < | x | false |
| x | = | x | true |
+--------------+----------+---------------+-------+
如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/evaluate-boolean-expression
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.*,
       case when operator = '>' and b.value > c.value then 'true'
            when operator = '<' and b.value < c.value then 'true'
            when operator = '=' and b.value = c.value then 'true'
        else 'false' end value
from Expressions a,Variables b,Variables c
where a.left_operand = b.name
  and a.right_operand = c.name

1445. 苹果和桔子

表: Sales

写一个 SQL 查询, 报告每一天苹果和桔子销售的数目的差异.

返回的结果表, 按照格式为 ('YYYY-MM-DD') 的 sale_date 排序.

查询结果表如下例所示:

Sales 表:
+------------+------------+-------------+
| sale_date | fruit | sold_num |
+------------+------------+-------------+
| 2020-05-01 | apples | 10 |
| 2020-05-01 | oranges | 8 |
| 2020-05-02 | apples | 15 |
| 2020-05-02 | oranges | 15 |
| 2020-05-03 | apples | 20 |
| 2020-05-03 | oranges | 0 |
| 2020-05-04 | apples | 15 |
| 2020-05-04 | oranges | 16 |
+------------+------------+-------------+

Result 表:
+------------+--------------+
| sale_date | diff |
+------------+--------------+
| 2020-05-01 | 2 |
| 2020-05-02 | 0 |
| 2020-05-03 | 20 |
| 2020-05-04 | -1 |
+------------+--------------+

在 2020-05-01, 卖了 10 个苹果和 8 个桔子 (差异为 10 - 8 = 2).
在 2020-05-02, 卖了 15 个苹果和 15 个桔子 (差异为 15 - 15 = 0).
在 2020-05-03, 卖了 20 个苹果和 0 个桔子 (差异为 20 - 0 = 20).
在 2020-05-04, 卖了 15 个苹果和 16 个桔子 (差异为 15 - 16 = -1).

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/apples-oranges
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select sale_date,
       sum(case when fruit = 'apples' then sold_num end) - 
       sum(case when fruit = 'oranges' then sold_num end) diff
from Sales
group by sale_date

1454. 活跃用户

表 Accounts:

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表主键.
该表包含账户 id 和账户的用户名.

表 Logins:

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| login_date | date |
+---------------+---------+
该表无主键, 可能包含重复项.
该表包含登录用户的账户 id 和登录日期. 用户也许一天内登录多次.

写一个 SQL 查询, 找到活跃用户的 id 和 name.

活跃用户是指那些至少连续 5 天登录账户的用户.

返回的结果表按照 id 排序.

结果表格式如下例所示:

Accounts 表:
+----+----------+
| id | name |
+----+----------+
| 1 | Winston |
| 7 | Jonathan |
+----+----------+

Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7 | 2020-05-30 |
| 1 | 2020-05-30 |
| 7 | 2020-05-31 |
| 7 | 2020-06-01 |
| 7 | 2020-06-02 |
| 7 | 2020-06-02 |
| 7 | 2020-06-03 |
| 1 | 2020-06-07 |
| 7 | 2020-06-10 |
+----+------------+

Result 表:
+----+----------+
| id | name |
+----+----------+
| 7 | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/active-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct c.id,d.name from 
(select b.*,
       datediff(lead(login_date,4) over(partition by b.id order by login_date),login_date) cnt
from 
(select distinct a.* from Logins a) b) c,Accounts d
where c.cnt = 4
  and c.id = d.id
order by c.id

1468. 计算税后工资

Salaries 表：

写一条查询 SQL 来查找每个员工的税后工资

每个公司的税率计算依照以下规则

如果这个公司员工最高工资不到 1000 ，税率为 0%
如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%
如果这个公司员工最高工资大于 10000 ，税率为 49%
按任意顺序返回结果，税后工资结果取整

结果表格式如下例所示：

Salaries 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1 | 1 | Tony | 2000 |
| 1 | 2 | Pronub | 21300 |
| 1 | 3 | Tyrrox | 10800 |
| 2 | 1 | Pam | 300 |
| 2 | 7 | Bassem | 450 |
| 2 | 9 | Hermione | 700 |
| 3 | 7 | Bocaben | 100 |
| 3 | 2 | Ognjen | 2200 |
| 3 | 13 | Nyancat | 3300 |
| 3 | 15 | Morninngcat | 7777 |
+------------+-------------+---------------+--------+

Result 表：
+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1 | 1 | Tony | 1020 |
| 1 | 2 | Pronub | 10863 |
| 1 | 3 | Tyrrox | 5508 |
| 2 | 1 | Pam | 300 |
| 2 | 7 | Bassem | 450 |
| 2 | 9 | Hermione | 700 |
| 3 | 7 | Bocaben | 76 |
| 3 | 2 | Ognjen | 1672 |
| 3 | 13 | Nyancat | 2508 |
| 3 | 15 | Morninngcat | 5911 |
+------------+-------------+---------------+--------+
对于公司 1 ，最高工资是 21300 ，其每个员工的税率为 49%
对于公司 2 ，最高工资是 700 ，其每个员工税率为 0%
对于公司 3 ，最高工资是 7777 ，其每个员工税率是 24%
税后工资计算 = 工资 - ( 税率 / 100）*工资
对于上述案例，Morninngcat 的税后工资 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 ，取整为 5911

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/calculate-salaries
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select c.company_id,c.employee_id,c.employee_name,
       round(c.salary - c.salary * (b.cnt/100),0) salary  
from Salaries c,
(select a.company_id, 
       case when a.salary_max < 1000 then 0
            when a.salary_max >= 1000 and a.salary_max <= 10000 then 24
            when a.salary_max > 10000 then 49 end cnt
from 
(select company_id,max(salary) salary_max from Salaries group by company_id) a) b 
where c.company_id = b.company_id

1501. 可以放心投资的国家

表 Person:

表 Calls:

+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id | int |
| callee_id | int |
| duration | int |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id
一家电信公司想要投资新的国家. 该公司想要投资的国家是: 该国的平均通话时长要严格地大于全球平均通话时长.

写一段 SQL, 找到所有该公司可以投资的国家.

返回的结果表没有顺序要求.

查询的结果格式如下例所示.

Person 表:
+----+----------+--------------+
| id | name | phone_number |
+----+----------+--------------+
| 3 | Jonathan | 051-1234567 |
| 12 | Elvis | 051-7654321 |
| 1 | Moncef | 212-1234567 |
| 2 | Maroua | 212-6523651 |
| 7 | Meir | 972-1234567 |
| 9 | Rachel | 972-0011100 |
+----+----------+--------------+

Country 表:
+----------+--------------+
| name | country_code |
+----------+--------------+
| Peru | 051 |
| Israel | 972 |
| Morocco | 212 |
| Germany | 049 |
| Ethiopia | 251 |
+----------+--------------+

Calls 表:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1 | 9 | 33 |
| 2 | 9 | 4 |
| 1 | 2 | 59 |
| 3 | 12 | 102 |
| 3 | 12 | 330 |
| 12 | 3 | 5 |
| 7 | 9 | 13 |
| 7 | 1 | 3 |
| 9 | 7 | 1 |
| 1 | 7 | 7 |
+-----------+-----------+----------+

Result 表:
+----------+
| country |
+----------+
| Peru |
+----------+
国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000
全球平均通话时长 = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/countries-you-can-safely-invest-in
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with tmp_calls as
(
    select caller_id caller,duration from Calls 
    union all
    select callee_id caller,duration from Calls
)

select distinct name country from 
(select left(phone_number,3) code,c.name,
       avg(a.duration) duration_avg
from tmp_calls a,Person b,Country c 
where a.caller = b.id
  and left(phone_number,3) = country_code 
group by left(phone_number,3)) a where a.duration_avg > (select avg(duration) from Calls)

1532. 最近的三笔订单

表：Customers

表：Orders

写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。

返回的结果按照 customer_name 升序排列。如果排名有相同，则继续按照 customer_id 升序排列。如果排名还有相同，则继续按照 order_date 降序排列。

查询结果格式如下例所示：

Customers
+-------------+-----------+
| customer_id | name |
+-------------+-----------+
| 1 | Winston |
| 2 | Jonathan |
| 3 | Annabelle |
| 4 | Marwan |
| 5 | Khaled |
+-------------+-----------+

Orders
+----------+------------+-------------+------+
| order_id | order_date | customer_id | cost |
+----------+------------+-------------+------+
| 1 | 2020-07-31 | 1 | 30 |
| 2 | 2020-07-30 | 2 | 40 |
| 3 | 2020-07-31 | 3 | 70 |
| 4 | 2020-07-29 | 4 | 100 |
| 5 | 2020-06-10 | 1 | 1010 |
| 6 | 2020-08-01 | 2 | 102 |
| 7 | 2020-08-01 | 3 | 111 |
| 8 | 2020-08-03 | 1 | 99 |
| 9 | 2020-08-07 | 2 | 32 |
| 10 | 2020-07-15 | 1 | 2 |
+----------+------------+-------------+------+

Result table：
+---------------+-------------+----------+------------+
| customer_name | customer_id | order_id | order_date |
+---------------+-------------+----------+------------+
| Annabelle | 3 | 7 | 2020-08-01 |
| Annabelle | 3 | 3 | 2020-07-31 |
| Jonathan | 2 | 9 | 2020-08-07 |
| Jonathan | 2 | 6 | 2020-08-01 |
| Jonathan | 2 | 2 | 2020-07-30 |
| Marwan | 4 | 4 | 2020-07-29 |
| Winston | 1 | 8 | 2020-08-03 |
| Winston | 1 | 1 | 2020-07-31 |
| Winston | 1 | 10 | 2020-07-15 |
+---------------+-------------+----------+------------+
Winston 有 4 笔订单, 排除了 "2020-06-10" 的订单, 因为它是最老的订单。
Annabelle 只有 2 笔订单, 全部返回。
Jonathan 恰好有 3 笔订单。
Marwan 只有 1 笔订单。
结果表我们按照 customer_name 升序排列，customer_id 升序排列，order_date 降序排列。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/the-most-recent-three-orders
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select name customer_name,customer_id,order_id,order_date 
from 
(select a.*,b.name,dense_rank() over(partition by customer_id order by order_date desc) cnt
from Orders a,Customers b 
where a.customer_id = b.customer_id) c
where c.cnt <= 3
order by c.name,c.customer_id,c.order_date desc

1549. 每件商品的最新订单

表: Customers

表: Orders

表: Products

写一个SQL 语句, 找到每件商品的最新订单(可能有多个).

返回的结果以 product_name 升序排列, 如果有排序相同, 再以 product_id 升序排列. 如果还有排序相同, 再以 order_id 升序排列.

查询结果格式如下例所示:

Customers
+-------------+-----------+
| customer_id | name |
+-------------+-----------+
| 1 | Winston |
| 2 | Jonathan |
| 3 | Annabelle |
| 4 | Marwan |
| 5 | Khaled |
+-------------+-----------+

Orders
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1 | 2020-07-31 | 1 | 1 |
| 2 | 2020-07-30 | 2 | 2 |
| 3 | 2020-08-29 | 3 | 3 |
| 4 | 2020-07-29 | 4 | 1 |
| 5 | 2020-06-10 | 1 | 2 |
| 6 | 2020-08-01 | 2 | 1 |
| 7 | 2020-08-01 | 3 | 1 |
| 8 | 2020-08-03 | 1 | 2 |
| 9 | 2020-08-07 | 2 | 3 |
| 10 | 2020-07-15 | 1 | 2 |
+----------+------------+-------------+------------+

Result
+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard | 1 | 6 | 2020-08-01 |
| keyboard | 1 | 7 | 2020-08-01 |
| mouse | 2 | 8 | 2020-08-03 |
| screen | 3 | 3 | 2020-08-29 |
+--------------+------------+----------+------------+
keyboard 的最新订单在2020-08-01, 在这天有两次下单.
mouse 的最新订单在2020-08-03, 在这天只有一次下单.
screen 的最新订单在2020-08-29, 在这天只有一次下单.
hard disk 没有被下单, 我们不把它包含在结果表中.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/the-most-recent-orders-for-each-product
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select c.product_name,b.product_id,b.order_id,b.order_date  
from 
(select a.*,dense_rank() over(partition by product_id order by order_date desc) cnt
from Orders a) b,Products c
where cnt = 1
  and b.product_id = c.product_id 
order by c.product_name,b.product_id,b.order_id

1555. 银行账户概要

用户表： Users

交易表：Transactions

力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表 Transaction 中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。

写一条 SQL 语句，查询：

user_id 用户 ID
user_name 用户名
credit 完成交易后的余额
credit_limit_breached 检查是否透支（"Yes" 或 "No"）
以任意顺序返回结果表。

查询格式见如下示例：

Users 表：
+------------+--------------+-------------+
| user_id | user_name | credit |
+------------+--------------+-------------+
| 1 | Moustafa | 100 |
| 2 | Jonathan | 200 |
| 3 | Winston | 10000 |
| 4 | Luis | 800 |
+------------+--------------+-------------+

Transactions 表：
+------------+------------+------------+----------+---------------+
| trans_id | paid_by | paid_to | amount | transacted_on |
+------------+------------+------------+----------+---------------+
| 1 | 1 | 3 | 400 | 2020-08-01 |
| 2 | 3 | 2 | 500 | 2020-08-02 |
| 3 | 2 | 1 | 200 | 2020-08-03 |
+------------+------------+------------+----------+---------------+

结果表：
+------------+------------+------------+-----------------------+
| user_id | user_name | credit | credit_limit_breached |
+------------+------------+------------+-----------------------+
| 1 | Moustafa | -100 | Yes |
| 2 | Jonathan | 500 | No |
| 3 | Winston | 9900 | No |
| 4 | Luis | 800 | No |
+------------+------------+------------+-----------------------+
Moustafa 在 "2020-08-01" 支付了 $400 并在 "2020-08-03" 收到了 $200 ，当前额度 (100 -400 +200) = -$100
Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ，当前额度 (200 +500 -200) = $500
Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ，当前额度 (10000 +400 -500) = $9900
Luis 未收到任何转账信息，额度 = $800

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/bank-account-summary
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.user_id,a.user_name,
       a.credit + coalesce(c.amount_to,0) - coalesce(b.amount_by,0) credit,
       case when a.credit + coalesce(c.amount_to,0) - coalesce(b.amount_by,0) < 0 then 'Yes' else 'No' end credit_limit_breached              
from Users a left join
(select paid_by,sum(amount) amount_by from Transactions group by paid_by) b
on a.user_id = b.paid_by left join 
(select paid_to,sum(amount) amount_to from Transactions group by paid_to) c   
on a.user_id = c.paid_to

1596. 每位顾客最经常订购的商品

表：Customers

表：Orders

表：Products

写一个 SQL 语句，找到每一个顾客最经常订购的商品。

结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。

返回结果没有顺序要求。

查询结果格式如下例所示：

Customers
+-------------+-------+
| customer_id | name |
+-------------+-------+
| 1 | Alice |
| 2 | Bob |
| 3 | Tom |
| 4 | Jerry |
| 5 | John |
+-------------+-------+

Orders
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1 | 2020-07-31 | 1 | 1 |
| 2 | 2020-07-30 | 2 | 2 |
| 3 | 2020-08-29 | 3 | 3 |
| 4 | 2020-07-29 | 4 | 1 |
| 5 | 2020-06-10 | 1 | 2 |
| 6 | 2020-08-01 | 2 | 1 |
| 7 | 2020-08-01 | 3 | 3 |
| 8 | 2020-08-03 | 1 | 2 |
| 9 | 2020-08-07 | 2 | 3 |
| 10 | 2020-07-15 | 1 | 2 |
+----------+------------+-------------+------------+

Products
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1 | keyboard | 120 |
| 2 | mouse | 80 |
| 3 | screen | 600 |
| 4 | hard disk | 450 |
+------------+--------------+-------+
Result 表：
+-------------+------------+--------------+
| customer_id | product_id | product_name |
+-------------+------------+--------------+
| 1 | 2 | mouse |
| 2 | 1 | keyboard |
| 2 | 2 | mouse |
| 2 | 3 | screen |
| 3 | 3 | screen |
| 4 | 1 | keyboard |
+-------------+------------+--------------+

Alice (customer 1) 三次订购鼠标, 一次订购键盘, 所以鼠标是 Alice 最经常订购的商品.
Bob (customer 2) 一次订购键盘, 一次订购鼠标, 一次订购显示器, 所以这些都是 Bob 最经常订购的商品.
Tom (customer 3) 只两次订购显示器, 所以显示器是 Tom 最经常订购的商品.
Jerry (customer 4) 只一次订购键盘, 所以键盘是 Jerry 最经常订购的商品.
John (customer 5) 没有订购过商品, 所以我们并没有把 John 包含在结果表中.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/the-most-frequently-ordered-products-for-each-customer
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select c.customer_id,c.product_id,d.product_name from 
(select b.*,dense_rank() over(partition by b.customer_id order by cnt desc) rnk 
from 
(select a.customer_id,a.product_id,count(*) cnt 
from Orders a
group by a.customer_id,a.product_id) b) c,Products d 
where c.product_id = d.product_id 
  and c.rnk = 1

1613. 找到遗失的ID

表: Customers

写一个 SQL 语句, 找到所有遗失的顾客id. 遗失的顾客id是指那些不在 Customers 表中, 值却处于 1 和表中最大 customer_id 之间的id.

注意: 最大的 customer_id 值不会超过 100.

返回结果按 ids 升序排列

查询结果格式如下例所示.

Customers 表:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1 | Alice |
| 4 | Bob |
| 5 | Charlie |
+-------------+---------------+

Result 表:
+-----+
| ids |
+-----+
| 2 |
| 3 |
+-----+
表中最大的customer_id是5, 所以在范围[1,5]内, ID2和3从表中遗失.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-the-missing-ids
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with recursive tmp(n) as (
  select 1
	union all
	select n+1 
	  from tmp
	where 1=1
	  and n<(select max(customer_id) from Customers)
)

select n ids from tmp where not exists(select * from Customers where customer_id = n)
order by ids

1699. 两人之间的通话次数

表： Calls

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| from_id | int |
| to_id | int |
| duration | int |
+-------------+---------+
该表没有主键，可能存在重复项。
该表包含 from_id 与 to_id 间的一次电话的时长。
from_id != to_id

编写 SQL 语句，查询每一对用户 (person1, person2) 之间的通话次数和通话总时长，其中 person1 < person2 。

以任意顺序返回结果表。

查询结果格式如下示例所示：

Calls 表：
+---------+-------+----------+
| from_id | to_id | duration |
+---------+-------+----------+
| 1 | 2 | 59 |
| 2 | 1 | 11 |
| 1 | 3 | 20 |
| 3 | 4 | 100 |
| 3 | 4 | 200 |
| 3 | 4 | 200 |
| 4 | 3 | 499 |
+---------+-------+----------+

结果表：
+---------+---------+------------+----------------+
| person1 | person2 | call_count | total_duration |
+---------+---------+------------+----------------+
| 1 | 2 | 2 | 70 |
| 1 | 3 | 1 | 20 |
| 3 | 4 | 4 | 999 |
+---------+---------+------------+----------------+
用户 1 和 2 打过 2 次电话，总时长为 70 (59 + 11)。
用户 1 和 3 打过 1 次电话，总时长为 20。
用户 3 和 4 打过 4 次电话，总时长为 999 (100 + 200 + 200 + 499)。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/number-of-calls-between-two-persons
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select from_id person1,to_id person2,count(*) call_count,sum(duration) total_duration   
from 
(select from_id,to_id,duration from Calls where from_id < to_id 
union all
select to_id,from_id,duration from Calls where from_id > to_id) a 
group by from_id,to_id

1709. 访问日期之间最大的空档期

表： UserVisits

假设今天的日期是 '2021-1-1' 。

编写 SQL 语句，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。

返回结果表，按用户编号 user_id 排序。

查询格式如下示例所示：

UserVisits 表：
+---------+------------+
| user_id | visit_date |
+---------+------------+
| 1 | 2020-11-28 |
| 1 | 2020-10-20 |
| 1 | 2020-12-3 |
| 2 | 2020-10-5 |
| 2 | 2020-12-9 |
| 3 | 2020-11-11 |
+---------+------------+
结果表：
+---------+---------------+
| user_id | biggest_window|
+---------+---------------+
| 1 | 39 |
| 2 | 65 |
| 3 | 51 |
+---------+---------------+
对于第一个用户，问题中的空档期在以下日期之间：
- 2020-10-20 至 2020-11-28 ，共计 39 天。
- 2020-11-28 至 2020-12-3 ，共计 5 天。
- 2020-12-3 至 2021-1-1 ，共计 29 天。
由此得出，最大的空档期为 39 天。
对于第二个用户，问题中的空档期在以下日期之间：
- 2020-10-5 至 2020-12-9 ，共计 65 天。
- 2020-12-9 至 2021-1-1 ，共计 23 天。
由此得出，最大的空档期为 65 天。
对于第三个用户，问题中的唯一空档期在 2020-11-11 至 2021-1-1 之间，共计 51 天。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/biggest-window-between-visits
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct d.user_id,d.cnt biggest_window from 
(select c.*,dense_rank() over(partition by c.user_id order by c.cnt desc) rnk from 
(select b.user_id,datediff(b.visit2,b.visit_date) cnt
from 
(select a.*,
       lead(visit_date,1,'2021-01-01') over(partition by user_id order by visit_date) visit2
from UserVisits a) b) c) d 
where d.rnk = 1

1715. 苹果和橘子的个数

表： Boxes

+--------------+------+
| Column Name | Type |
+--------------+------+
| box_id | int |
| chest_id | int |
| apple_count | int |
| orange_count | int |
+--------------+------+
box_id 是该表的主键。
chest_id 是 chests 表的外键。
该表包含大箱子 (box) 中包含的苹果和橘子的个数。每个大箱子中可能包含一个小盒子 (chest) ，小盒子中也包含若干苹果和橘子。

表： Chests

+--------------+------+
| Column Name | Type |
+--------------+------+
| chest_id | int |
| apple_count | int |
| orange_count | int |
+--------------+------+
chest_id 是该表的主键。
该表包含小盒子的信息，以及小盒子中包含的苹果和橘子的个数。

编写 SQL 语句，查询每个大箱子中苹果和橘子的个数。如果大箱子中包含小盒子，还应当包含小盒子中苹果和橘子的个数。

以任意顺序返回结果表。

查询结果的格式如下示例所示：

Boxes 表：
+--------+----------+-------------+--------------+
| box_id | chest_id | apple_count | orange_count |
+--------+----------+-------------+--------------+
| 2 | null | 6 | 15 |
| 18 | 14 | 4 | 15 |
| 19 | 3 | 8 | 4 |
| 12 | 2 | 19 | 20 |
| 20 | 6 | 12 | 9 |
| 8 | 6 | 9 | 9 |
| 3 | 14 | 16 | 7 |
+--------+----------+-------------+--------------+

Chests 表：
+----------+-------------+--------------+
| chest_id | apple_count | orange_count |
+----------+-------------+--------------+
| 6 | 5 | 6 |
| 14 | 20 | 10 |
| 2 | 8 | 8 |
| 3 | 19 | 4 |
| 16 | 19 | 19 |
+----------+-------------+--------------+

结果表：
+-------------+--------------+
| apple_count | orange_count |
+-------------+--------------+
| 151 | 123 |
+-------------+--------------+
大箱子 2 中有 6 个苹果和 15 个橘子。
大箱子 18 中有 4 + 20 (在小盒子中) = 24 个苹果和 15 + 10 (在小盒子中) = 25 个橘子。
大箱子 19 中有 8 + 19 (在小盒子中) = 27 个苹果和 4 + 4 (在小盒子中) = 8 个橘子。
大箱子 12 中有 19 + 8 (在小盒子中) = 27 个苹果和 20 + 8 (在小盒子中) = 28 个橘子。
大箱子 20 中有 12 + 5 (在小盒子中) = 17 个苹果和 9 + 6 (在小盒子中) = 15 个橘子。
大箱子 8 中有 9 + 5 (在小盒子中) = 14 个苹果和 9 + 6 (在小盒子中) = 15 个橘子。
大箱子 3 中有 16 + 20 (在小盒子中) = 36 个苹果和 7 + 10 (在小盒子中) = 17 个橘子。
苹果的总个数 = 6 + 24 + 27 + 27 + 17 + 14 + 36 = 151
橘子的总个数 = 15 + 25 + 8 + 28 + 15 + 15 + 17 = 123

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/count-apples-and-oranges
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select sum(apple_count_all) apple_count,sum(orange_count_all) orange_count from 
(select a.*,coalesce(b.apple_count,0) apple_count_b,coalesce(b.orange_count,0) apple_count_c,
       a.apple_count + coalesce(b.apple_count,0) apple_count_all,
       a.orange_count + coalesce(b.orange_count,0) orange_count_all
from Boxes a left join Chests b 
on a.chest_id = b.chest_id) c

1747. 应该被禁止的Leetflex账户

表: LogInfo

编写一个SQL查询语句，查找那些应该被禁止的Leetflex帐户编号account_id。如果某个帐户在某一时刻从两个不同的网络地址登录了，则这个帐户应该被禁止。

可以以任何顺序返回结果。

查询结果格式如下例所示:

LogInfo table:
+------------+------------+---------------------+---------------------+
| account_id | ip_address | login | logout |
+------------+------------+---------------------+---------------------+
| 1 | 1 | 2021-02-01 09:00:00 | 2021-02-01 09:30:00 |
| 1 | 2 | 2021-02-01 08:00:00 | 2021-02-01 11:30:00 |
| 2 | 6 | 2021-02-01 20:30:00 | 2021-02-01 22:00:00 |
| 2 | 7 | 2021-02-02 20:30:00 | 2021-02-02 22:00:00 |
| 3 | 9 | 2021-02-01 16:00:00 | 2021-02-01 16:59:59 |
| 3 | 13 | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
| 4 | 10 | 2021-02-01 16:00:00 | 2021-02-01 17:00:00 |
| 4 | 11 | 2021-02-01 17:00:00 | 2021-02-01 17:59:59 |
+------------+------------+---------------------+---------------------+

Result table:
+------------+
| account_id |
+------------+
| 1 |
| 4 |
+------------+
Account ID 1 --> 该账户从 "2021-02-01 09:00:00" 到 "2021-02-01 09:30:00" 在两个不同的网络地址(1 and 2)上激活了。它应该被禁止.
Account ID 2 --> 该账户在两个不同的网络地址 (6, 7) 激活了，但在不同的时间上.
Account ID 3 --> 该账户在两个不同的网络地址 (9, 13) 激活了，虽然是同一天，但时间上没有交集.
Account ID 4 --> 该账户从 "2021-02-01 17:00:00" 到 "2021-02-01 17:00:00" 在两个不同的网络地址 (10 and 11)上激活了。它应该被禁止.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/leetflex-banned-accounts
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select distinct a.account_id from LogInfo a where exists(select * from LogInfo b where a.account_id = b.account_id and a.ip_address <> b.ip_address and b.login >= a.login and b.login <= a.logout)

1783. 大满贯数量

表：Players

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| year | int |
| Wimbledon | int |
| Fr_open | int |
| US_open | int |
| Au_open | int |
+---------------+---------+
year 是这个表的主键
该表的每一行都包含在每场大满贯网球比赛中赢得比赛的球员的 ID

请写出查询语句，查询出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。

结果集无顺序要求。

查询结果的格式，如下所示：

Players 表：
+-----------+-------------+
| player_id | player_name |
+-----------+-------------+
| 1 | Nadal |
| 2 | Federer |
| 3 | Novak |
+-----------+-------------+

Championships 表：
+------+-----------+---------+---------+---------+
| year | Wimbledon | Fr_open | US_open | Au_open |
+------+-----------+---------+---------+---------+
| 2018 | 1 | 1 | 1 | 1 |
| 2019 | 1 | 1 | 2 | 2 |
| 2020 | 2 | 1 | 2 | 2 |
+------+-----------+---------+---------+---------+

Result 表：
+-----------+-------------+-------------------+
| player_id | player_name | grand_slams_count |
+-----------+-------------+-------------------+
| 2 | Federer | 5 |
| 1 | Nadal | 7 |
+-----------+-------------+-------------------+

Player 1 (Nadal) 获得了 7 次大满贯：其中温网 2 次(2018, 2019), 法国公开赛 3 次 (2018, 2019, 2020), 美国公开赛 1 次 (2018)以及澳网公开赛 1 次 (2018) 。
Player 2 (Federer) 获得了 5 次大满贯：其中温网 1 次 (2020), 美国公开赛 2 次 (2019, 2020) 以及澳网公开赛 2 次 (2019, 2020) 。
Player 3 (Novak) 没有赢得，因此不包含在结果集中。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/grand-slam-titles
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
select a.player_id,b.player_name,count(*) grand_slams_count from 
(select Wimbledon player_id from Championships
union all
select Fr_open from Championships
union all
select US_open from Championships
union all 
select Au_open from Championships) a,Players b 
where a.player_id = b.player_id
group by a.player_id

1811. 寻找面试候选人

表: Contests

+--------------+------+
| Column Name | Type |
+--------------+------+
| contest_id | int |
| gold_medal | int |
| silver_medal | int |
| bronze_medal | int |
+--------------+------+
contest_id 是该表的主键.
该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。
可以保证，所有连续的比赛都有连续的ID，没有ID被跳过。

Table: Users

编写 SQL 语句来返回面试候选人的姓名和邮件.当用户满足以下两个要求中的任意一条，其成为面试候选人:

该用户在连续三场及更多比赛中赢得奖牌。
该用户在三场及更多不同的比赛中赢得金牌（这些比赛可以不是连续的）
可以以任何顺序返回结果。

查询结果格式如下例所示：

Contests表:
+------------+------------+--------------+--------------+
| contest_id | gold_medal | silver_medal | bronze_medal |
+------------+------------+--------------+--------------+
| 190 | 1 | 5 | 2 |
| 191 | 2 | 3 | 5 |
| 192 | 5 | 2 | 3 |
| 193 | 1 | 3 | 5 |
| 194 | 4 | 5 | 2 |
| 195 | 4 | 2 | 1 |
| 196 | 1 | 5 | 2 |
+------------+------------+--------------+--------------+

Sarah 赢得了3块金牌 (190, 193, and 196),所以我们将她列入结果表。
Bob在连续3场竞赛中赢得了奖牌(190, 191, and 192), 所以我们将他列入结果表。
- 注意他在另外的连续3场竞赛中也赢得了奖牌(194, 195, and 196).
Alice在连续3场竞赛中赢得了奖牌 (191, 192, and 193), 所以我们将她列入结果表。
Quarz在连续5场竞赛中赢得了奖牌(190, 191, 192, 193, and 194), 所以我们将他列入结果表。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/find-interview-candidates
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with tmp as
(
    select contest_id,gold_medal medal from Contests
    union all 
    select contest_id,silver_medal medal from Contests
    union all
    select contest_id,bronze_medal medal from Contests
)

select e.name,e.mail from 
(select distinct a.medal 
from tmp a join tmp b 
on a.contest_id = b.contest_id - 1
and a.medal = b.medal join tmp c 
on a.contest_id = c.contest_id - 2
and a.medal = c.medal 
union
select gold_medal from Contests group by gold_medal having count(*) >= 3) d,Users e 
where d.medal  = e.user_id

1841. 联赛信息统计

Table: Teams

Table: Matches

+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| home_team_id | int |
| away_team_id | int |
| home_team_goals | int |
| away_team_goals | int |
+-----------------+---------+
(home_team_id, away_team_id) 是该表主键.
每一行包含了一次比赛信息.
home_team_goals 代表主场队得球数.
away_team_goals 代表客场队得球数.
获得球数较多的队伍为胜者队伍.

写一段SQL，用来报告联赛信息. 统计数据应使用已进行的比赛来构建，其中获胜球队获得三分，而失败球队获得零分. 如果打平，两支球队都得一分.

result表的每行应包含以下信息:

team_name - Teams表中的队伍名字
matches_played - 主场与客场球队进行的比赛次数.
points - 球队获得的总分数.
goal_for - 球队在所有比赛中获取的总进球数
goal_against - 球队在所有比赛中，他的对手球队的所有进球数
goal_diff - goal_for - goal_against.
按分数降序返回结果表。如果两队或多队得分相同，则按goal_diff 降序排列。如果仍然存在平局，则以 team_name 按字典顺序排列它们。

查询的结果格式如下例所示:

Teams table:
+---------+-----------+
| team_id | team_name |
+---------+-----------+
| 1 | Ajax |
| 4 | Dortmund |
| 6 | Arsenal |
+---------+-----------+

Matches table:
+--------------+--------------+-----------------+-----------------+
| home_team_id | away_team_id | home_team_goals | away_team_goals |
+--------------+--------------+-----------------+-----------------+
| 1 | 4 | 0 | 1 |
| 1 | 6 | 3 | 3 |
| 4 | 1 | 5 | 2 |
| 6 | 1 | 0 | 0 |
+--------------+--------------+-----------------+-----------------+

Result table:
+-----------+----------------+--------+----------+--------------+-----------+
| team_name | matches_played | points | goal_for | goal_against | goal_diff |
+-----------+----------------+--------+----------+--------------+-----------+
| Dortmund | 2 | 6 | 6 | 2 | 4 |
| Arsenal | 2 | 2 | 3 | 3 | 0 |
| Ajax | 4 | 2 | 5 | 9 | -4 |
+-----------+----------------+--------+----------+--------------+-----------+

Ajax (team_id=1) 有4场比赛: 2败2平. 总分数 = 0 + 0 + 1 + 1 = 2.
Dortmund (team_id=4) 有2场比赛: 2胜. 总分数 = 3 + 3 = 6.
Arsenal (team_id=6) 有2场比赛: 2平. 总分数 = 1 + 1 = 2.
Dortmund 是积分榜上的第一支球队. Ajax和Arsenal 有同样的分数, 但Arsenal的goal_diff高于Ajax, 所以Arsenal在表中的顺序在Ajaxzhi'qian.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/league-statistics
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with tmp as 
(select home_team_id team_id,
       home_team_goals team_goals,away_team_goals,
       case when home_team_goals > away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            when home_team_goals < away_team_goals then 0 end points
from Matches
union all 
select away_team_id team_id,
       away_team_goals team_goals,home_team_goals,
       case when home_team_goals < away_team_goals then 3
            when home_team_goals = away_team_goals then 1
            when home_team_goals > away_team_goals then 0 end points
from Matches)

select b.team_name,
       count(*) matches_played,
       sum(points) points,
       sum(team_goals) goal_for,
       sum(away_team_goals) goal_against,
       sum(team_goals) - sum(away_team_goals) goal_diff   
from tmp a,Teams b
where a.team_id = b.team_id 
group by a.team_id
order by points desc,goal_diff desc,b.team_name

1907. 按分类统计薪水

表: Accounts

+-------------+------+
| 列名 | 类型 |
+-------------+------+
| account_id | int |
| income | int |
+-------------+------+
account_id 是这个表的主键。
每一行都包含一个银行帐户的月收入的信息。
写出一个 SQL 查询，来报告每个工资类别的银行账户数量。工资类别如下：

“低薪”：所有工资严格低于20000美元。
“中等薪水”：包含范围内的所有工资 [$20000, $50000]。
“高薪”：所有工资严格大于50000美元。

结果表必须包含所有三个类别。如果某个类别中没有帐户，则报告 0。

按任意顺序返回结果表。

查询结果格式如下示例：

Accounts 表:
+------------+--------+
| account_id | income |
+------------+--------+
| 3 | 108939 |
| 2 | 12747 |
| 8 | 87709 |
| 6 | 91796 |
+------------+--------+

Result 表:
+----------------+----------------+
| category | accounts_count |
+----------------+----------------+
| Low Salary | 1 |
| Average Salary | 0 |
| High Salary | 3 |
+----------------+----------------+

低薪: 数量为 2.
中等薪水: 没有.
高薪: 有三个账户，他们是 3, 6和 8.

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/count-salary-categories
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

# Write your MySQL query statement below
with tmp as 
(
    select 'Low Salary' category
    union
    select 'Average Salary' category
    union 
    select 'High Salary' category
)

select a.category,coalesce(accounts_count,0) accounts_count from tmp a left join
(select category,count(*) accounts_count from 
(select income,
       case when income < 20000 then 'Low Salary'
            when income > 50000 then 'High Salary'
            else 'Average Salary' end category
from Accounts) a  
group by a.category) b
on a.category = b.category

原文链接：https://blog.csdn.net/thomashhs12/article/details/119350497