81-100

81. 获取最近第二次的活动

写一条SQL查询展示每一位用户 最近第二次 的活动，如果用户仅有一次活动，返回该活动。一个用户不能同时进行超过一项活动，以 任意 顺序返回结果。

展示效果：

+------------+--------------+-------------+-------------+
| username   | activity     | startDate   | endDate     |
+------------+--------------+-------------+-------------+
| Alice      | Dancing      | 2020-02-21  | 2020-02-23  |
| Bob        | Travel       | 2020-02-11  | 2020-02-18  |
+------------+--------------+-------------+-------------+

建表语句：

Create table If Not Exists 81_UserActivity (username varchar(30), activity varchar(30), startDate date, endDate date);
Truncate table 81_UserActivity;
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-12', '2020-02-20');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Dancing', '2020-02-21', '2020-02-23');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Alice', 'Travel', '2020-02-24', '2020-02-28');
insert into 81_UserActivity (username, activity, startDate, endDate) values ('Bob', 'Travel', '2020-02-11', '2020-02-18');

最终SQL：

-- 方法一
select 
     max(u1.username) username,
     max(u1.activity) activity,
     max(u1.startdate) startdate,
     max(u1.enddate) enddate
from 
     81_useractivity u1 
join
     81_useractivity u2
on
     u1.username = u2.username
group by
     u1.username, u1.startdate
having
     sum(if(u2.startdate > u1.startdate,1,0)) = 1
     or
     count(1) = 1;
-- 方法二
select 
     `username`,
     activity,
     startDate,
     endDate 
from 
    (select 
          username,
          activity,
          startDate,
          endDate ,
          rank() over(partition by username order by startDate desc) rk,
          lag(startDate ,1, null) over(partition by username order by startDate ) lg
     from 81_UserActivity) t1
where
     rk=2 or (rk = 1 and lg is null)

82. 使用唯一标识码替换员工ID

写一段SQL查询来展示每位用户的 唯一标识码（unique ID ）；如果某位员工没有唯一标识码，使用 null 填充即可。你可以以 任意 顺序返回结果表。

展示效果：

+-----------+----------+
| unique_id | name     |
+-----------+----------+
| null      | Alice    |
| null      | Bob      |
| 2         | Meir     |
| 3         | Winston  |
| 1         | Jonathan |
+-----------+----------+

建表语句：

Create table If Not Exists 82_Employees (id int, name varchar(20));
Create table If Not Exists 82_EmployeeUNI (id int, unique_id int);
Truncate table 82_Employees;
insert into 82_Employees (id, name) values ('1', 'Alice');
insert into 82_Employees (id, name) values ('7', 'Bob');
insert into 82_Employees (id, name) values ('11', 'Meir');
insert into 82_Employees (id, name) values ('90', 'Winston');
insert into 82_Employees (id, name) values ('3', 'Jonathan');
Truncate table 82_EmployeeUNI;
insert into 82_EmployeeUNI (id, unique_id) values ('3', '1');
insert into 82_EmployeeUNI (id, unique_id) values ('11', '2');
insert into 82_EmployeeUNI (id, unique_id) values ('90', '3');

最终SQL：

select
     if(unique_id is null, null, unique_id) as unique_id,
     e.name
from 
     82_Employees e 
left join
     82_EmployeeUNI u
on
     e.id = u.id;

83. 按年度列出销售总额

编写一段SQL查询每个产品每年的总销售额，并包含 product_id, product_name 以及 report_year 等信息。销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。

展示效果：

+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1          | LC Phone     |    2019     | 3500         |
| 2          | LC T-Shirt   |    2018     | 310          |
| 2          | LC T-Shirt   |    2019     | 3650         |
| 2          | LC T-Shirt   |    2020     | 10           |
| 3          | LC Keychain  |    2019     | 31           |
| 3          | LC Keychain  |    2020     | 31           |
+------------+--------------+-------------+--------------+
LC Phone 在 2019-01-25 至 2019-02-28 期间销售，该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售，该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天，2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售，该产品在2019年、2020年的销售时间分别是：31天、31天，2019年、2020年的销售总额分别是31*1=31、31*1=31。

建表语句：

Create table If Not Exists 83_Product (product_id int, product_name varchar(30));
Create table If Not Exists 83_Sales (product_id varchar(30), period_start date, period_end date, average_daily_sales int);
Truncate table 83_Product;
insert into 83_Product (product_id, product_name) values ('1', 'LC Phone ');
insert into 83_Product (product_id, product_name) values ('2', 'LC T-Shirt');
insert into 83_Product (product_id, product_name) values ('3', 'LC Keychain');
Truncate table 83_Sales;
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values ('1', '2019-01-25', '2019-02-28', '100');
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values ('2', '2018-12-01', '2020-01-01', '10');
insert into 83_Sales (product_id, period_start, period_end, average_daily_sales) values ('3', '2019-12-01', '2020-01-31', '1');

最终SQL：

    (select
           s1.product_id,
           product_name,
          '2018' as 'report_year', 
          if(period_start<'2019-01-01',(datediff(
                                           if(period_end<'2019-01-01', period_end, date('2018-12-31')),
                                           if(period_start>='2018-01-01', period_start, date('2018-01-01'))
                                           )+1
                                        ) * average_daily_sales, 0) as total_amount
     from 
          83_Sales as s1
     join 
          83_Product as p1
     on 
          s1.product_id = p1.product_id 
     having
          total_amount>0 )
union
    (select
          s2.product_id,
          product_name,
         '2019' as 'report_year',
         if( period_start<'2020-01-01', (datediff(
                                             if(period_end<'2020-01-01', period_end, date('2019-12-31')),                                                            if(period_start>='2019-01-01', period_start, date('2019-01-01'))
                                             )+1
                                        ) * average_daily_sales , 0) as total_amount
     from
          83_Sales as s2
     join
          83_Product as p2
     on
         s2.product_id = p2.product_id 
     having  total_amount>0)
union
    (select 
          s3.product_id,
          product_name,
          '2020' as 'report_year', 
          (datediff(
                   if(period_end<'2021-01-01', period_end, date('2020-12-31')),
                   if(period_start>='2020-01-01', period_start, date('2020-01-01'))
                   )+1
           ) * average_daily_sales as total_amount
     from 
          83_Sales as s3 
     join 
          83_Product as p3
     on 
          s3.product_id = p3.product_id
     having total_amount>0 ) 
order by product_id, report_year

84. 股票的资本损益

编写一个SQL查询来报告每支股票的资本损益。股票的资本损益是一次或多次买卖股票后的全部收益或损失。以任意顺序返回结果即可。

展示效果：

+---------------+-------------------+
| 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美元。

Create Table If Not Exists 84_Stocks(stock_name varchar(15),operation ENUM('Sell', 'Buy'),operation_day int, price int);
Truncate table 84_Stocks;
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Buy', '1', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '2', '10');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Leetcode', 'Sell', '5', '9000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Buy', '17', '30000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '3', '1010');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '4', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '5', '500');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Buy', '6', '1000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Handbags', 'Sell', '29', '7000');
insert into 84_Stocks (stock_name, operation, operation_day, price) values ('Corona Masks', 'Sell', '10', '10000');

最终SQL：

-- 方法一
select
     s2.stock_name,
     s2.sum_sell - s1.sum_buy as capital_gain_loss
from
    (select 
           stock_name,
           sum(if(operation='Buy',price,0)) as sum_buy,
           sum(if(operation='Sell',price,0)) as sum_sell
     from
           84_stocks
     group by 
           stock_name, operation) s1
join
    (select 
           stock_name,
           sum(if(operation='Buy',price,0)) as sum_buy,
           sum(if(operation='Sell',price,0)) as sum_sell
     from
           84_stocks
     group by 
           stock_name, operation) s2
on
    s1.stock_name = s2.stock_name
where 
    s1.sum_buy <> 0 
    and
    s2.sum_buy = 0;
-- 方法二
select 
     stock_name,sell-buy capital_gain_loss
from
    (select
          stock_name,
          sum(if(operation='Buy', price,0))over(partition by stock_name ) buy,
          sum(if(operation='Sell',price,0))over(partition by stock_name) sell
     from
          84_Stocks s )t1
group by
     stock_name,buy,sell;

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

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

展示效果：

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+

建表语句：

Create table If Not Exists 85_Customers (customer_id int, customer_name varchar(30));
Create table If Not Exists 85_Orders (order_id int, customer_id int, product_name varchar(30));
Truncate table 85_Customers;
insert into 85_Customers (customer_id, customer_name) values ('1', 'Daniel');
insert into 85_Customers (customer_id, customer_name) values ('2', 'Diana');
insert into 85_Customers (customer_id, customer_name) values ('3', 'Elizabeth');
insert into 85_Customers (customer_id, customer_name) values ('4', 'Jhon');
Truncate table 85_Orders;
insert into 85_Orders (order_id, customer_id, product_name) values ('10', '1', 'A');
insert into 85_Orders (order_id, customer_id, product_name) values ('20', '1', 'B');
insert into 85_Orders (order_id, customer_id, product_name) values ('30', '1', 'D');
insert into 85_Orders (order_id, customer_id, product_name) values ('40', '1', 'C');
insert into 85_Orders (order_id, customer_id, product_name) values ('50', '2', 'A');
insert into 85_Orders (order_id, customer_id, product_name) values ('60', '3', 'A');
insert into 85_Orders (order_id, customer_id, product_name) values ('70', '3', 'B');
insert into 85_Orders (order_id, customer_id, product_name) values ('80', '3', 'D');
insert into 85_Orders (order_id, customer_id, product_name) values ('90', '4', 'C');

最终SQL：

select
     o.customer_id,
     customer_name 
from 
     85_Orders o 
left join
     85_Customers c
on
     o.customer_id=c.customer_id
group by
     customer_id
having 
     sum(product_name ='A')>=1 
     and
     sum(product_name='B')>=1
     and
     sum(product_name='C')=0;

86. 排名靠前的旅行者

写一段 SQL , 报告每个用户的旅行距离。返回的结果表单, 以 travelled_distance 降序排列, 如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列.

展示效果：

+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
Elvis 和 Lee 旅行了 450 英里, Elvis 是排名靠前的旅行者, 因为他的名字在字母表上的排序比 Lee 更小.
Bob, Jonathan, Alex 和 Alice 只有一次行程, 我们只按此次行程的全部距离对他们排序.
Donald 没有任何行程, 他的旅行距离为 0.

建表语句：

Create Table If Not Exists 86_Users (id int, name varchar(30));
Create Table If Not Exists 86_Rides (id int, user_id int, distance int);
Truncate table 86_Users;
insert into 86_Users (id, name) values ('1', 'Alice');
insert into 86_Users (id, name) values ('2', 'Bob');
insert into 86_Users (id, name) values ('3', 'Alex');
insert into 86_Users (id, name) values ('4', 'Donald');
insert into 86_Users (id, name) values ('7', 'Lee');
insert into 86_Users (id, name) values ('13', 'Jonathan');
insert into 86_Users (id, name) values ('19', 'Elvis');
Truncate table 86_Rides;
insert into 86_Rides (id, user_id, distance) values ('1', '1', '120');
insert into 86_Rides (id, user_id, distance) values ('2', '2', '317');
insert into 86_Rides (id, user_id, distance) values ('3', '3', '222');
insert into 86_Rides (id, user_id, distance) values ('4', '7', '100');
insert into 86_Rides (id, user_id, distance) values ('5', '13', '312');
insert into 86_Rides (id, user_id, distance) values ('6', '19', '50');
insert into 86_Rides (id, user_id, distance) values ('7', '7', '120');
insert into 86_Rides (id, user_id, distance) values ('8', '19', '400');
insert into 86_Rides (id, user_id, distance) values ('9', '7', '230');

最终SQL：

select
      name,
      sum(ifnull(distance,0)) travelled_distance 
from 
      86_Users u 
left join 
      86_Rides r
on 
      u.id = r.user_id
group by
      name
order by
      travelled_distance  desc, name;

87. 查找成绩处于中游的学生

写一个 SQL 语句，找出在所有测验中都处于中游的学生 (student_id, student_name)。成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。不要返回从来没有参加过测验的学生。返回结果表按照 student_id 排序。

展示效果：

+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
对于测验 1: 学生 1 和 3 分别获得了最低分和最高分。
对于测验 2: 学生 1 既获得了最高分, 也获得了最低分。
对于测验 3 和 4: 学生 1 和 4 分别获得了最低分和最高分。
学生 2 和 5 没有在任一场测验中获得了最高分或者最低分。
因为学生 5 从来没有参加过任何测验, 所以他被排除于结果表。
由此, 我们仅仅返回学生 2 的信息。

建表语句：

Create table If Not Exists 87_Student (student_id int, student_name varchar(30));
Create table If Not Exists 87_Exam (exam_id int, student_id int, score int);
Truncate table 87_Student;
insert into 87_Student (student_id, student_name) values ('1', 'Daniel');
insert into 87_Student (student_id, student_name) values ('2', 'Jade');
insert into 87_Student (student_id, student_name) values ('3', 'Stella');
insert into 87_Student (student_id, student_name) values ('4', 'Jonathan');
insert into 87_Student (student_id, student_name) values ('5', 'Will');
Truncate table 87_Exam;
insert into 87_Exam (exam_id, student_id, score) values ('10', '1', '70');
insert into 87_Exam (exam_id, student_id, score) values ('10', '2', '80');
insert into 87_Exam (exam_id, student_id, score) values ('10', '3', '90');
insert into 87_Exam (exam_id, student_id, score) values ('20', '1', '80');
insert into 87_Exam (exam_id, student_id, score) values ('30', '1', '70');
insert into 87_Exam (exam_id, student_id, score) values ('30', '3', '80');
insert into 87_Exam (exam_id, student_id, score) values ('30', '4', '90');
insert into 87_Exam (exam_id, student_id, score) values ('40', '1', '60');
insert into 87_Exam (exam_id, student_id, score) values ('40', '2', '70');
insert into 87_Exam (exam_id, student_id, score) values ('40', '4', '80');

最终SQL：

select
      student_id,
      student_name
from 
      87_student
where
      student_id not in(
                 select
                       s1.student_id
                 from 
                       87_student s1
                 left join
                       87_exam e1
                 on
                       s1.student_id = e1.student_id
                 join
                      (select max(score) max_score, min(score) min_score from 87_Exam) m
                 on 
                       e1.score = m.max_score
                       or
                       e1.score = m.min_score
                       or
                       e1.score is null);
-- 方法二
select 
      e.student_id,
      student_name
from 
      87_Exam e 
left join
      87_Student s
on 
      e.student_id=s.student_id
where 
      e.student_id not in(select 
                                student_id
                          from
                               (select 
                                      student_id,
                                      rank() over(partition by exam_id order by score desc) rkmax,
                                      rank() over(partition by exam_id order by score ) rkmin
                                from 
                                      87_Exam )t1
                          where 
                                rkmax = 1 or rkmin =1 )
group by 
      e.student_id,
      student_name
order by
      e.student_id;

88. 净现值查询

写一个 SQL, 找到 Queries 表中每一次查询的净现值，结果表没有顺序要求.

展示效果：

+------+--------+--------+
| 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 表中找到.

Create Table If Not Exists 88_NPV (id int, year int, npv int);
Create Table If Not Exists 88_Queries (id int, year int);
Truncate table 88_NPV;
insert into 88_NPV (id, year, npv) values ('1', '2018', '100');
insert into 88_NPV (id, year, npv) values ('7', '2020', '30');
insert into 88_NPV (id, year, npv) values ('13', '2019', '40');
insert into 88_NPV (id, year, npv) values ('1', '2019', '113');
insert into 88_NPV (id, year, npv) values ('2', '2008', '121');
insert into 88_NPV (id, year, npv) values ('3', '2009', '21');
insert into 88_NPV (id, year, npv) values ('11', '2020', '99');
insert into 88_NPV (id, year, npv) values ('7', '2019', '0');
Truncate table 88_Queries;
insert into 88_Queries (id, year) values ('1', '2019');
insert into 88_Queries (id, year) values ('2', '2008');
insert into 88_Queries (id, year) values ('3', '2009');
insert into 88_Queries (id, year) values ('7', '2018');
insert into 88_Queries (id, year) values ('7', '2019');
insert into 88_Queries (id, year) values ('7', '2020');
insert into 88_Queries (id, year) values ('13', '2019');

最终SQL：

select
      q.id,
      q.year,
      ifnull(npv,0) npv
from 
      88_Queries q
left join
      88_NPV n
on 
      q.id = n.id 
      and
      q.year = n.year;

89. 制作会话柱状图

你想知道用户在你的 app 上的访问时长情况。因此决定统计访问时长区间分别为 “[0-5>”, “[5-10>”, “[10-15>” 和 “15 or more” （单位：分钟）的会话数量，并以此绘制柱状图。

写一个SQL查询来报告（访问时长区间，会话总数）。结果可用任何顺序呈现。

展示效果：

+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+
对于 session_id 1，2 和 3 ，它们的访问时间大于等于 0 分钟且小于 5 分钟。
对于 session_id 4，它的访问时间大于等于 5 分钟且小于 10 分钟。
没有会话的访问时间大于等于 10 分钟且小于 15 分钟。
对于 session_id 5, 它的访问时间大于等于 15 分钟。

Create table If Not Exists 89_Sessions (session_id int, duration int);
Truncate table 89_Sessions;
insert into 89_Sessions (session_id, duration) values ('1', '30');
insert into 89_Sessions (session_id, duration) values ('2', '199');
insert into 89_Sessions (session_id, duration) values ('3', '299');
insert into 89_Sessions (session_id, duration) values ('4', '580');
insert into 89_Sessions (session_id, duration) values ('5', '1000');

最终SQL：

-- 方法一
select '[0-5>' as bin, count(*) as total from 89_Sessions where duration/60>=0 and duration/60<5
union
select '[5-10>' as bin, count(*) as total from 89_Sessions where duration/60>=5 and duration/60<10
union
select '[10-15>' as bin, count(*) as total from 89_Sessions where duration/60>=10 and duration/60<15
union
select '15 or more'as bin, count(*) as total from 89_Sessions where duration/60>=15
-- 方法二
select a.bin, count(b.bin) as total
from
(select '[0-5>' as bin union select '[5-10>' as bin union select '[10-15>' as bin union select '15 or more' as bin)a
left join 
(select case
        when duration < 300 then '[0-5>'
        when duration >= 300 and duration < 600 then '[5-10>'
        when duration >= 600 and duration < 900 then '[10-15>'
        else '15 or more'
        end bin
    from 89_Sessions 
)b
on a.bin = b.bin
group by a.bin

90. 计算布尔表达式的值

写一个 SQL 查询, 以计算表 Expressions 中的布尔表达式，返回的结果表没有顺序要求.

展示效果：

+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+

建表语句：

Create Table If Not Exists 90_Variables (name varchar(3), value int);
Create Table If Not Exists 90_Expressions (left_operand varchar(3), operator ENUM('>', '<', '='), right_operand varchar(3));
Truncate table 90_Variables;
insert into 90_Variables (name, value) values ('x', '66');
insert into 90_Variables (name, value) values ('y', '77');
Truncate table 90_Expressions;
insert into 90_Expressions (left_operand, operator, right_operand) values ('x', '>', 'y');
insert into 90_Expressions (left_operand, operator, right_operand) values ('x', '<', 'y');
insert into 90_Expressions (left_operand, operator, right_operand) values ('x', '=', 'y');
insert into 90_Expressions (left_operand, operator, right_operand) values ('y', '>', 'x');
insert into 90_Expressions (left_operand, operator, right_operand) values ('y', '<', 'x');
insert into 90_Expressions (left_operand, operator, right_operand) values ('x', '=', 'x');

最终SQL：

select
     e.left_operand,
     e.operator,
     e.right_operand,
     case e.operator
              when '>' then if(v1.value>v2.value,'true','false')
              when '<' then if(v1.value<v2.value,'true','false')
              else if(v1.value=v2.value,'true','false')
     end value
from 
     90_Expressions e
left join
     90_Variables v1 
on
     v1.name = e.left_operand 
left join
     90_Variables v2 
on
     v2.name = e.right_operand;

91. 苹果和桔子

写一个 SQL 查询, 报告每一天 苹果 和 桔子 销售的数目的差异.返回的结果表, 按照格式为 (‘YYYY-MM-DD’) 的 sale_date 排序.

查询结果表如下例所示：

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

Create table If Not Exists 91_Sales (sale_date date, fruit ENUM('apples', 'oranges'), sold_num int);
Truncate table 91_Sales;
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'apples', '10');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-01', 'oranges', '8');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'apples', '15');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-02', 'oranges', '15');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'apples', '20');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-03', 'oranges', '0');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'apples', '15');
insert into 91_Sales (sale_date, fruit, sold_num) values ('2020-05-04', 'oranges', '16');

-- 方法一
select
     s1.sale_date,
     s1.sold_num - s2.sold_num as diff
from 
     91_Sales s1
join 
     91_Sales s2
on 
     s1.sale_date = s2.sale_date
     and
     s1.fruit <> s2.fruit
     and 
     s1.fruit <> 'oranges';
-- 方法二
select  
     sale_date,
     sold_num - ld as diff
from 
    (select
          sale_date,
          sold_num, 
          fruit,
          lead(sold_num ,1,null) over(partition by  sale_date ) ld
     from 91_Sales )t1
where 
     fruit='apples';

92. 活跃用户

写一个 SQL 查询, 找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户。返回的结果表按照 id 排序.

展示数据：

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

建表语句：

Create table If Not Exists 92_Accounts (id int, name varchar(10));
Create table If Not Exists 92_Logins (id int, login_date date);
Truncate table 92_Accounts;
insert into 92_Accounts (id, name) values ('1', 'Winston');
insert into 92_Accounts (id, name) values ('7', 'Jonathan');
Truncate table 92_Logins;
insert into 92_Logins (id, login_date) values ('7', '2020-05-30');
insert into 92_Logins (id, login_date) values ('1', '2020-05-30');
insert into 92_Logins (id, login_date) values ('7', '2020-05-31');
insert into 92_Logins (id, login_date) values ('7', '2020-06-01');
insert into 92_Logins (id, login_date) values ('7', '2020-06-02');
insert into 92_Logins (id, login_date) values ('7', '2020-06-02');
insert into 92_Logins (id, login_date) values ('7', '2020-06-03');
insert into 92_Logins (id, login_date) values ('1', '2020-06-07');
insert into 92_Logins (id, login_date) values ('7', '2020-06-10');

最终SQL:

-- 方法一
select
      distinct a.id,
      a.name 
from 
      92_Accounts a,
      92_logins l1
where 
      a.id=l1.id
      and
      (select
             count(distinct l2.login_date)  
       from 
             92_logins l2
       where 
             l1.id=l2.id
             and 
             datediff(l1.login_date,l2.login_date) between 0 and 4
       )>=5
order by id;
-- 方法二
select
      distinct a.id, a.name 
from 
      92_Accounts a
join 
      92_Logins l1
using(id)
join 
      92_Logins l2
on
      l1.id = l2.id
      and
      datediff(l2.login_date, l1.login_date) between 0 and 4
group by
      a.id, a.name, l1.login_date
having
      count(distinct l2.login_date) = 5;
-- 方法三
select 
     t3.id,name
from 
    (select
           distinct id
    from 
          (select
                 id,
                 login_date,
                 lead(login_date,4,null) over(partition by id order by login_date) ld
           from 
               (select
                     id,
                     login_date 
                from 
                     92_Logins
                group by 
                     id,login_date
               )t1
           )t2
    where datediff(ld,login_date)=4 
    )t3
left join
      92_Accounts a
on 
      t3.id = a.id;

93. 矩形面积

写一个 SQL 语句, 报告由表中任意两点可以形成的所有可能的矩形.

结果表中的每一行包含三列 (p1, p2, area) 如下:

• p1 和 p2 是矩形两个对角的 id 且 p1 < p2.
• 矩形的面积由列 area 表示.

请按照面积大小降序排列，如果面积相同的话, 则按照 p1 和 p2 升序对结果表排序

+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 6           |
| 1        | 2           | 2           |
+----------+-------------+-------------+
p1 应该小于 p2 并且面积大于 0.
p1 = 1 且 p2 = 2 时, 面积等于 |2-4| * |8-7| = 2.
p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |7-10| = 6.
p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 因为面积等于 0.

Create table If Not Exists 93_Points (id int, x_value int, y_value int);
Truncate table 93_Points;
insert into 93_Points (id, x_value, y_value) values ('1', '2', '8');
insert into 93_Points (id, x_value, y_value) values ('2', '4', '7');
insert into 93_Points (id, x_value, y_value) values ('3', '2', '10');

最终SQL：

select 
      a.id P1,
      b.id P2,
      abs(a.x_value-b.x_value)*abs(a.y_value-b.y_value) as area
from 
      Points a,Points b
where 
      a.id<b.id 
      and 
      a.x_value != b.x_value
      and
      a.y_value != b.y_value
order by
      area desc,P1 ,P2