LeetCode数据库SQL练习（八）练习题目来自：https://leetcode-cn.com/ 即时食物配送系列

练习题目来自：leetcode-cn.com/

即时食物配送系列

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。
「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

1、写一条 SQL 查询语句获取即时订单所占的百分比， 保留两位小数。

查询结果如下所示：

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

Result 表:
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
2 和 3 号订单为即时订单，其他的为计划订单。

SQL1：

select
round(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end) / count(delivery_id) * 100, 2) immediate_percentage
from
Delivery

SQL2：

select
round (
    (select
    count(*)
    from
    Delivery
    where order_date = customer_pref_delivery_date) /
    (select
    count(*)
    from
    Delivery) * 100,
    2
) immediate_percentage

SQL3：

select
round (sum(order_date = customer_pref_delivery_date) / count(*) * 100, 2) immediate_percentage
from
Delivery

SQL4：

即时订单的平均数

select
round(avg(order_date = customer_pref_delivery_date) * 100, 2) immediate_percentage
from
Delivery

2、写一条 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 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。

SQL1：

select
round(avg(t.order_date = t.customer_pref_delivery_date) * 100, 2) immediate_percentage
from
    (select
    customer_id, order_date, customer_pref_delivery_date,
    ROW_NUMBER() over(partition by customer_id order by order_date) rn
    from
    Delivery) t
where t.rn = 1

SQL2：

select
round (sum(order_date = customer_pref_delivery_date) * 100 / count(*), 2) immediate_percentage
from
Delivery
where (customer_id, order_date) in (select
                                    customer_id, min(order_date)
                                    from
                                    Delivery
                                    group by customer_id)

重新格式化部门表

部门表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

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

Department 表：
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

当一列中有多个数据时，case when只会提取当中的第一个数据，使用聚合函数可以输出所有的数据。

SQL1：

select
id,
sum(case month when 'Jan' then revenue end) Jan_Revenue,
sum(case month when 'Feb' then revenue end) Feb_Revenue,
sum(case month when 'Mar' then revenue end) Mar_Revenue,
sum(case month when 'Apr' then revenue end) Apr_Revenue,
sum(case month when 'May' then revenue end) May_Revenue,
sum(case month when 'Jun' then revenue end) Jun_Revenue,
sum(case month when 'Jul' then revenue end) Jul_Revenue,
sum(case month when 'Aug' then revenue end) Aug_Revenue,
sum(case month when 'Sep' then revenue end) Sep_Revenue,
sum(case month when 'Oct' then revenue end) Oct_Revenue,
sum(case month when 'Nov' then revenue end) Nov_Revenue,
sum(case month when 'Dec' then revenue end) Dec_Revenue
from
Department
group by id

SQL2：

select
id,
max(case month when 'Jan' then revenue end) Jan_Revenue,
max(case month when 'Feb' then revenue end) Feb_Revenue,
max(case month when 'Mar' then revenue end) Mar_Revenue,
max(case month when 'Apr' then revenue end) Apr_Revenue,
max(case month when 'May' then revenue end) May_Revenue,
max(case month when 'Jun' then revenue end) Jun_Revenue,
max(case month when 'Jul' then revenue end) Jul_Revenue,
max(case month when 'Aug' then revenue end) Aug_Revenue,
max(case month when 'Sep' then revenue end) Sep_Revenue,
max(case month when 'Oct' then revenue end) Oct_Revenue,
max(case month when 'Nov' then revenue end) Nov_Revenue,
max(case month when 'Dec' then revenue end) Dec_Revenue
from
Department
group by id

每月交易系列

Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“，”拒绝“] 之一。

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| charge_date    | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易，即使未经批准。

1、编写一个 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                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+

SQL：

select
date_format(trans_date, '%Y-%m') month,
country,
count(id) trans_count,
sum(state = 'approved') approved_count,
sum(amount) trans_total_amount,
sum(if(state = 'approved', amount, 0)) approved_total_amount
from
Transactions
group by month, country

2、编写一个 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               |
+----------+---------+----------------+-----------------+-------------------+--------------------+

SQL：

select
month, country,
sum(if(tag = 1, 1, 0)) approved_count,
sum(if(tag = 1, amount, 0)) approved_amount,
sum(if(tag = 0, 1, 0)) chargeback_count,
sum(if(tag = 0, amount, 0)) chargeback_amount
from (select
      country, amount, 1 tag, date_format(trans_date, '%Y-%m') month
      from
      Transactions
      where state='approved'
      union all
      select
      country, amount, 0 tag, date_format(c.trans_date, '%Y-%m') month
      from
      Transactions t right join Chargebacks c
      on t.id = c.trans_id) t
group by month, country

锦标赛优胜者

Players玩家表

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id 是此表的主键。
此表的每一行表示每个玩家的组。

Matches赛事表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id 是此表的主键。
每一行是一场比赛的记录，first_player 和 second_player 表示该场比赛的球员 ID。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小的选手获胜。
编写一个 SQL 查询来查找每组中的获胜者。

查询结果格式如下所示

Players 表:
+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 15        | 1          |
| 25        | 1          |
| 30        | 1          |
| 45        | 1          |
| 10        | 2          |
| 35        | 2          |
| 50        | 2          |
| 20        | 3          |
| 40        | 3          |
+-----------+------------+

Matches 表:
+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 15           | 45            | 3           | 0            |
| 2          | 30           | 25            | 1           | 2            |
| 3          | 30           | 15            | 2           | 0            |
| 4          | 40           | 20            | 5           | 2            |
| 5          | 35           | 50            | 1           | 1            |
+------------+--------------+---------------+-------------+--------------+

Result 表:
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

SQL1：

合并查询

select
t1.group_id, t1.player_id
from
    (select
    t.group_id, t.player_id, sum(t.score) score
    from
        (select
        p.group_id, p.player_id, sum(m.first_score) score
        from
        Players p join Matches m
        on p.player_id = m.first_player
        group by p.player_id
        union all
        select
        p.group_id, p.player_id, sum(m.second_score) score
        from
        Players p join Matches m
        on p.player_id = m.second_player
        group by p.player_id) t
    group by player_id
    order by score DESC, player_id) t1
group by group_id

SQL2：

窗口函数

select
distinct t2.group_id, t2.first_player player_id
from
    (select
    *,
    RANK() over(partition by group_id order by first_score DESC, first_player) rk
    from
        (select
        t.first_player, sum(t.first_score) first_score
        from
            (select
            first_player, first_score
            from
            Matches
            union all
            select
            second_player, second_score
            from
            Matches) t
        group by first_player) t1 left join Players p
    on p.player_id = t1.first_player) t2
where t2.rk = 1

SQL3：

判断每场比赛，选手是 first_player 还是 second_player ，如个是 first_player 就累加 first_score ，如果是 second_player 就累加 second_score。

select
t.group_id, t.player_id
from
    (select
    group_id, player_id,
    RANK() over(partition by group_id order by sum(if(player_id = first_player, first_score, second_score)) DESC, player_id) rk
    from
    Players p, Matches m
    where p.player_id = m.first_player or p.player_id = m.second_player
    group by group_id, player_id) t
where t.rk = 1

最后一个能进入电梯的人

表: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id 是这个表的主键。
该表展示了所有等待电梯的人的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。

电梯最大载重量为 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) 是最后一个体重合适并进入电梯的人。

SQL1：

自连接

select
a.person_name
from
Queue a, Queue b
where a.turn >= b.turn
group by a.person_id
having sum(b.weight) <= 1000
order by a.turn DESC
limit 1

SQL2：

变量

select
t1.person_name
from
    (select
    person_name,
    @a := @a + weight weight
    from
    Queue, (select @a := 0) t
    order by turn) t1
where t1.weight <= 1000
order by t1.weight DESC
limit 1

SQL3：

窗口函数
- 统计从起点到当前行

select
t.person_name
from
    (select
    person_name,
    sum(weight) over(order by turn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
    from
    Queue) t
where t.sum <= 1000
order by t.sum DESC
limit 1

查询结果的质量和占比

查询表 Queries：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
此表没有主键，并可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

查询结果格式如下所示：

Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

SQL：

select
query_name,
round(avg(rating / position), 2) quality,
round(avg(if(rating < 3, 1, 0)) * 100, 2) poor_query_percentage
from
Queries
group by query_name

查询球队积分

Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
此表的主键是 team_id，表中的每一行都代表一支独立足球队。

Table: Matches

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

SQL1：

合并查询

select
t2.team_id, t2.team_name, ifnull(t1.sum, 0) num_points
from
    (select
    t.team_id, sum(t.score) sum
    from
        (select
        host_team team_id,
        sum(case
        when host_goals > guest_goals then 3
        when host_goals < guest_goals then 0
        else 1
        end) score
        from
        Matches
        group by host_team
        union all
        select
        guest_team team_id,
        sum(case
        when guest_goals > host_goals then 3
        when guest_goals < host_goals then 0
        else 1
        end) score
        from
        Matches
        group by guest_team) t
    group by team_id) t1
right join Teams t2
on t1.team_id = t2.team_id
order by num_points DESC, t2.team_id

SQL2：

Join方法

select
team_id, team_name,
ifnull(sum(if(team_id = host_team, host_score, guest_score)), 0) num_points
from
Teams t
left join
(select
host_team, guest_team,
if(host_goals < guest_goals, 0, if(host_goals > guest_goals, 3, 1)) host_score,
if(host_goals < guest_goals, 3, if(host_goals > guest_goals, 0, 1)) guest_score
from
Matches) t1
on t.team_id = t1.host_team or t.team_id = t1.guest_team
group by team_id
order by num_points DESC, team_id

报告系统状态的连续日期

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.

系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+


Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。

EXTRACT()：
- 用于返回日期、时间的单独部分，比如年、月、日、小时、分钟等等。
判断一个时间在 2019 年的方法：
- between '2019-01-01' and '2019-12-31'
- extract(year from fail_date) = 2019

SQL1：

合并查询

select
'failed' period_state,
min(fail_date) start_date,
max(fail_date) end_date
from
    (select
    fail_date,
    subdate(fail_date, ROW_NUMBER() over()) first_date
    from
    Failed
    where extract(year from fail_date) = 2019) t1
group by first_date
union
select
'succeeded' period_state,
min(success_date) start_date,
max(success_date) end_date
from
    (select
    success_date,
    subdate(success_date, ROW_NUMBER() over()) first_date
    from
    Succeeded
    where extract(year from success_date) = 2019) t2
group by first_date
order by start_date

SQL2：

变量

select
period_state,
min(date) start_date,
max(date) end_date
from
    (select
    'succeeded' period_state,
    success_date date,
    if(datediff(@pre_date, @pre_date := success_date) = -1, @id, @id := @id + 1) rn
    from
    Succeeded,
    (select @pre_date := null, @id := 0) t
    union
    select
    'failed' period_state,
    fail_date date,
    if(datediff(@pre_date, @pre_date := fail_date) = -1, @id, @id := @id + 1) rn
    from
    Failed,
    (select @pre_date := null, @id := 0) t) t1
where date between '2019-01-01' and '2019-12-31'
group by period_state, rn
order by start_date