刷题记录帖子

1440. Evaluate Boolean Expression
Medium
Topics
SQL Schema
Pandas Schema
Table Variables:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
In SQL, name is the primary key for this table.
This table contains the stored variables and their values.


Table Expressions:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
In SQL, (left_operand, operator, right_operand) is the primary key for this table.
This table contains a boolean expression that should be evaluated.
operator is an enum that takes one of the values ('<', '>', '=')
The values of left_operand and right_operand are guaranteed to be in the Variables table.


Evaluate the boolean expressions in Expressions table.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Variables table:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+
Expressions table:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+
Output:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
Explanation:
As shown, you need to find the value of each boolean expression in the table using the variables table.

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     e.left_operand,
   
4.     e.operator,
   
5.     e.right_operand,
   
6.     CASE
   
7.         WHEN e.operator = '>' AND v1.value > v2.value THEN 'true'
   
8.         WHEN e.operator = '<' AND v1.value < v2.value THEN 'true'
   
9.         WHEN e.operator = '=' AND v1.value = v2.value THEN 'true'
   
10.         ELSE 'false'
    
11.     END AS value
    
12. FROM Expressions e
    
13. JOIN Variables v1 ON e.left_operand  = v1.name
    
14. JOIN Variables v2 ON e.right_operand = v2.name;

复制代码

1435. Create a Session Bar Chart
Easy
Topics
SQL Schema
Pandas Schema
Table: Sessions

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| session_id          | int     |
| duration            | int     |
+---------------------+---------+
session_id is the column of unique values for this table.
duration is the time in seconds that a user has visited the application.


You want to know how long a user visits your application. You decided to create bins of "[0-5>", "[5-10>", "[10-15>", and "15 minutes or more" and count the number of sessions on it.

Write a solution to report the (bin, total).

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Sessions table:
+-------------+---------------+
| session_id  | duration      |
+-------------+---------------+
| 1           | 30            |
| 2           | 199           |
| 3           | 299           |
| 4           | 580           |
| 5           | 1000          |
+-------------+---------------+
Output:
+--------------+--------------+
| bin          | total        |
+--------------+--------------+
| [0-5>        | 3            |
| [5-10>       | 1            |
| [10-15>      | 0            |
| 15 or more   | 1            |
+--------------+--------------+
Explanation:
For session_id 1, 2, and 3 have a duration greater or equal than 0 minutes and less than 5 minutes.
For session_id 4 has a duration greater or equal than 5 minutes and less than 10 minutes.
There is no session with a duration greater than or equal to 10 minutes and less than 15 minutes.
For session_id 5 has a duration greater than or equal to 15 minutes.

思路

要把会话时长分到 4 个区间里，统计每个区间有多少场会话。有三个细节：

单位不同：duration 是秒，区间是分钟。5 分钟 = 300 秒，10 分钟 = 600 秒，15 分钟 = 900 秒，所以实际的边界是 300、600、900。
区间是左闭右开：[0-5> 表示大于等于 0 分钟、小于 5 分钟。恰好 300 秒的会话属于 [5-10>，不属于 [0-5>。
空区间也要输出：[10-15> 里一场会话都没有，但这一行仍然要出现，total 为 0。

输出的标签以示例为准：最后一个区间叫 15 or more（题目文字里写的是 “15 minutes or more”，但示例输出是 15 or more）。

1. # Write your MySQL query statement below
   
2. SELECT '[0-5>' AS bin, COUNT(*) AS total
   
3. FROM Sessions
   
4. WHERE duration >= 0 AND duration < 300
   
5. 
   
6. UNION ALL
   
7. 
   
8. SELECT '[5-10>', COUNT(*)
   
9. FROM Sessions
   
10. WHERE duration >= 300 AND duration < 600
    
11. 
    
12. UNION ALL
    
13. 
    
14. SELECT '[10-15>', COUNT(*)
    
15. FROM Sessions
    
16. WHERE duration >= 600 AND duration < 900
    
17. 
    
18. UNION ALL
    
19. 
    
20. SELECT '15 or more', COUNT(*)
    
21. FROM Sessions
    
22. WHERE duration >= 900;

复制代码

逐步解释
四个独立的查询：每个查询统计一个区间的会话数，标签直接写成常量字符串。
空区间自然得 0：这些查询没有 GROUP BY，没有任何行满足条件时，COUNT(*) 返回 0 而不是空结果，所以 [10-15> 这一行会保留。如果加了 GROUP BY，空区间就会消失。
UNION ALL：把四个结果上下拼接。这里不需要去重，用 UNION ALL 也省掉了去重的开销。列名取自第一个查询，即 bin 和 total。
边界条件：每个区间都是 >= 下界 AND < 上界，区间之间首尾相接且不重叠，每场会话恰好落进一个区间。

1421. NPV Queries
Easy
Topics
SQL Schema
Pandas Schema
Table: NPV

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
| npv           | int     |
+---------------+---------+
(id, year) is the primary key (combination of columns with unique values) of this table.
The table has information about the id and the year of each inventory and the corresponding net present value.


Table: Queries

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| year          | int     |
+---------------+---------+
(id, year) is the primary key (combination of columns with unique values) of this table.
The table has information about the id and the year of each inventory query.


Write a solution to find the npv of each query of the Queries table.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
NPV table:
+------+--------+--------+
| 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 table:
+------+--------+
| id   | year   |
+------+--------+
| 1    | 2019   |
| 2    | 2008   |
| 3    | 2009   |
| 7    | 2018   |
| 7    | 2019   |
| 7    | 2020   |
| 13   | 2019   |
+------+--------+
Output:
+------+--------+--------+
| id   | year   | npv    |
+------+--------+--------+
| 1    | 2019   | 113    |
| 2    | 2008   | 121    |
| 3    | 2009   | 12     |
| 7    | 2018   | 0      |
| 7    | 2019   | 0      |
| 7    | 2020   | 30     |
| 13   | 2019   | 40     |
+------+--------+--------+
Explanation:
The npv value of (7, 2018) is not present in the NPV table, we consider it 0.
The npv values of all other queries can be found in the NPV table.

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     q.id,
   
4.     q.year,
   
5.     IFNULL(n.npv, 0) AS npv
   
6. FROM Queries q
   
7. LEFT JOIN NPV n
   
8.     ON q.id = n.id
   
9.    AND q.year = n.year;

复制代码

1412. Find the Quiet Students in All Exams
Hard
Topics
SQL Schema
Pandas Schema
Table: Student

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


Table: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the student with student_id had a score points in the exam with id exam_id.


A quiet student is the one who took at least one exam and did not score the highest or the lowest score.

Write a solution to report the students (student_id, student_name) being quiet in all exams. Do not return the student who has never taken any exam.

Return the result table ordered by student_id.

The result format is in the following example.



Example 1:

Input:
Student table:
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+
Exam table:
+------------+--------------+-----------+
| exam_id    | student_id   | score     |
+------------+--------------+-----------+
| 10         |     1        |    70     |
| 10         |     2        |    80     |
| 10         |     3        |    90     |
| 20         |     1        |    80     |
| 30         |     1        |    70     |
| 30         |     3        |    80     |
| 30         |     4        |    90     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+
Output:
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
Explanation:
For exam 1: Student 1 and 3 hold the lowest and high scores respectively.
For exam 2: Student 1 hold both highest and lowest score.
For exam 3 and 4: Student 1 and 4 hold the lowest and high scores respectively.
Student 2 and 5 have never got the highest or lowest in any of the exams.
Since student 5 is not taking any exam, he is excluded from the result.
So, we only return the information of Student 2.

思路

"安静的学生"需要同时满足：

至少参加过一次考试。
在所有考试里都不是最高分，也不是最低分。只要有一场考试拿了最高或最低，就不算安静。

做法是反过来想：

对每场考试算出最高分 mx 和最低分 mn。
把每条成绩和所在考试的 mx、mn 比较，等于其中之一的成绩就是"冒头"的成绩。
按学生汇总，一次冒头都没有的学生就是安静学生。
从 Student 出发做 INNER JOIN，从未参加考试的学生自然被排除。

1. # Write your MySQL query statement below
   
2. WITH scored AS (
   
3.     SELECT
   
4.         student_id,
   
5.         score,
   
6.         MAX(score) OVER (PARTITION BY exam_id) AS mx,
   
7.         MIN(score) OVER (PARTITION BY exam_id) AS mn
   
8.     FROM Exam
   
9. )
   
10. SELECT
    
11.     s.student_id,
    
12.     s.student_name
    
13. FROM Student s
    
14. JOIN scored r
    
15.     ON s.student_id = r.student_id
    
16. GROUP BY s.student_id, s.student_name
    
17. HAVING SUM(r.score = r.mx OR r.score = r.mn) = 0
    
18. ORDER BY s.student_id;

复制代码

逐步解释
scored：PARTITION BY exam_id 按考试分区，MAX 和 MIN 在每个分区内取最高、最低分，并附在该考试的每一行上，行数不变。
JOIN Student：用 INNER JOIN，没有任何成绩记录的学生（如 Will）在 scored 里没有对应行，会被排除，满足"没参加过考试的不返回"。
SUM(r.score = r.mx OR r.score = r.mn)：MySQL 里条件表达式的结果是 1 或 0，求和就是这个学生"拿过最高或最低分"的次数。
HAVING ... = 0：一次都没有，才是所有考试里都安静。
并列的情况：用 score = mx 判断，几个人并列最高分时，他们都会被标记为冒头，符合题意。
只有一个人参加的考试：这个人的分数同时等于 mx 和 mn，也会被标记（如考试 20 里的 Daniel）。
排序：按 student_id 升序。

1. # Write your MySQL query statement below
   
2. SELECT s.student_id, s.student_name
   
3. FROM Student s
   
4. WHERE s.student_id IN (SELECT student_id FROM Exam)
   
5.   AND s.student_id NOT IN (
   
6.       SELECT e.student_id
   
7.       FROM Exam e
   
8.       JOIN (
   
9.           SELECT exam_id, MAX(score) AS mx, MIN(score) AS mn
   
10.           FROM Exam
    
11.           GROUP BY exam_id
    
12.       ) t
    
13.           ON e.exam_id = t.exam_id
    
14.       WHERE e.score = t.mx OR e.score = t.mn
    
15.   )
    
16. ORDER BY s.student_id;

复制代码

第一个条件：学生至少有一条考试记录。
第二个条件：先找出所有"拿过最高或最低分"的学生，再用 NOT IN 排除。
NOT IN 是安全的：Exam.student_id 是主键的一部分，不会为 NULL。

1407. Top Travellers
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the column with unique values for this table.
name is the name of the user.


Table: Rides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+
id is the column with unique values for this table.
user_id is the id of the user who traveled the distance "distance".


Write a solution to report the distance traveled by each user.

Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

The result format is in the following example.



Example 1:

Input:
Users table:
+------+-----------+
| id   | name      |
+------+-----------+
| 1    | Alice     |
| 2    | Bob       |
| 3    | Alex      |
| 4    | Donald    |
| 7    | Lee       |
| 13   | Jonathan  |
| 19   | Elvis     |
+------+-----------+
Rides table:
+------+----------+----------+
| id   | user_id  | distance |
+------+----------+----------+
| 1    | 1        | 120      |
| 2    | 2        | 317      |
| 3    | 3        | 222      |
| 4    | 7        | 100      |
| 5    | 13       | 312      |
| 6    | 19       | 50       |
| 7    | 7        | 120      |
| 8    | 19       | 400      |
| 9    | 7        | 230      |
+------+----------+----------+
Output:
+----------+--------------------+
| name     | travelled_distance |
+----------+--------------------+
| Elvis    | 450                |
| Lee      | 450                |
| Bob      | 317                |
| Jonathan | 312                |
| Alex     | 222                |
| Alice    | 120                |
| Donald   | 0                  |
+----------+--------------------+
Explanation:
Elvis and Lee traveled 450 miles, Elvis is the top traveler as his name is alphabetically smaller than Lee.
Bob, Jonathan, Alex, and Alice have only one ride and we just order them by the total distances of the ride.
Donald did not have any rides, the distance traveled by him is 0.

思路

要算每个用户的总行驶距离，包括一次都没出行的用户（如 Donald），他的距离记为 0。

以 Users 为主表做 LEFT JOIN，连接 Rides，这样没有行程的用户也会保留。
按用户分组，对 distance 求和。
先按总距离降序排，距离相同再按名字升序排。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     u.name,
   
4.     IFNULL(SUM(r.distance), 0) AS travelled_distance
   
5. FROM Users u
   
6. LEFT JOIN Rides r
   
7.     ON u.id = r.user_id
   
8. GROUP BY u.id, u.name
   
9. ORDER BY travelled_distance DESC, u.name ASC;

复制代码

1398. Customers Who Bought Products A and B but Not C
Medium
Topics
SQL Schema
Pandas Schema
Table: Customers

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| customer_id         | int     |
| customer_name       | varchar |
+---------------------+---------+
customer_id is the column with unique values for this table.
customer_name is the name of the customer.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_name  | varchar |
+---------------+---------+
order_id is the column with unique values for this table.
customer_id is the id of the customer who bought the product "product_name".


Write a solution to report the customer_id and customer_name of customers who bought products "A", "B" but did not buy the product "C" since we want to recommend them to purchase this product.

Return the result table ordered by customer_id.

The result format is in the following example.



Example 1:

Input:
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         |
+------------+--------------+---------------+
Output:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 3           | Elizabeth     |
+-------------+---------------+
Explanation: Only the customer_id with id 3 bought the product A and B but not the product C.

思路

要找的是买过 A、买过 B、没买过 C 的顾客。三个条件针对的都是同一个顾客的全部订单，所以不能用 WHERE product_name = ... 逐行过滤，而要先按顾客分组，再在组内判断：

组内有没有 A。
组内有没有 B。
组内有没有 C，要求没有。

用条件聚合配合 HAVING 可以一次扫描完成。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     c.customer_id,
   
4.     c.customer_name
   
5. FROM Customers c
   
6. JOIN Orders o
   
7.     ON c.customer_id = o.customer_id
   
8. GROUP BY c.customer_id, c.customer_name
   
9. HAVING SUM(o.product_name = 'A') > 0
   
10.    AND SUM(o.product_name = 'B') > 0
    
11.    AND SUM(o.product_name = 'C') = 0
    
12. ORDER BY c.customer_id;

复制代码

逐步解释
JOIN Orders：Customers 里有姓名，Orders 里有购买记录，连起来后每个顾客有多少订单就有多少行。
SUM(o.product_name = 'A')：MySQL 里比较表达式的结果是 1 或 0，求和就是该顾客买 A 的订单数。大于 0 表示买过。
SUM(o.product_name = 'C') = 0：买 C 的订单数为 0，表示没买过。
条件要写在 HAVING 里：这三个条件依赖聚合结果，WHERE 里不能用。如果在 WHERE 里写 product_name IN ('A', 'B')，C 的订单会先被过滤掉，就没法判断"没买过 C"了。
用 INNER JOIN 就够了：一个订单都没有的顾客不可能买过 A 和 B，本来就该排除。
排序：按 customer_id 升序。

1393. Capital Gain/Loss
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Stocks

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| stock_name    | varchar |
| operation     | enum    |
| operation_day | int     |
| price         | int     |
+---------------+---------+
(stock_name, operation_day) is the primary key (combination of columns with unique values) for this table.
The operation column is an ENUM (category) of type ('Sell', 'Buy')
Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price.
It is guaranteed that each 'Sell' operation for a stock has a corresponding 'Buy' operation in a previous day. It is also guaranteed that each 'Buy' operation for a stock has a corresponding 'Sell' operation in an upcoming day.


Write a solution to report the Capital gain/loss for each stock.

The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Stocks table:
+---------------+-----------+---------------+--------+
| 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  |
+---------------+-----------+---------------+--------+
Output:
+---------------+-------------------+
| stock_name    | capital_gain_loss |
+---------------+-------------------+
| Corona Masks  | 9500              |
| Leetcode      | 8000              |
| Handbags      | -23000            |
+---------------+-------------------+
Explanation:
Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$.
Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$.
Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$. It was bought again at day 4 for 1000$ and was sold at day 5 for 500$. At last, it was bought at day 6 for 1000$ and was sold at day 10 for 10000$. Capital gain/loss is the sum of capital gains/losses for each ('Buy' --> 'Sell') operation = (1010 - 10) + (500 - 1000) + (10000 - 1000) = 1000 - 500 + 9000 = 9500$.

思路

这题看起来像是要把每次 Buy 和之后的 Sell 配对，再逐对算盈亏。但题目保证每次买入都有对应的卖出，每次卖出也都有对应的买入，所以不用配对。

每一对的盈亏是 卖出价 - 买入价，把所有配对加起来：

总盈亏 = Σ(卖出价) - Σ(买入价)

不管怎么配对，每笔买入都恰好出现在一对里，每笔卖出也一样，所以总和不变。因此只要按股票分组，把 Sell 的价格记为正、Buy 的价格记为负，再求和。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     stock_name,
   
4.     SUM(CASE WHEN operation = 'Sell' THEN price ELSE -price END) AS capital_gain_loss
   
5. FROM Stocks
   
6. GROUP BY stock_name;

复制代码

1468. Calculate Salaries
Medium
Topics
SQL Schema
Pandas Schema
Table Salaries:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| company_id    | int     |
| employee_id   | int     |
| employee_name | varchar |
| salary        | int     |
+---------------+---------+
In SQL,(company_id, employee_id) is the primary key for this table.
This table contains the company id, the id, the name, and the salary for an employee.


Find the salaries of the employees after applying taxes. Round the salary to the nearest integer.

The tax rate is calculated for each company based on the following criteria:

0% If the max salary of any employee in the company is less than $1000.
24% If the max salary of any employee in the company is in the range [1000, 10000] inclusive.
49% If the max salary of any employee in the company is greater than $10000.
Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Salaries table:
+------------+-------------+---------------+--------+
| 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   |
+------------+-------------+---------------+--------+
Output:
+------------+-------------+---------------+--------+
| 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   |
+------------+-------------+---------------+--------+
Explanation:
For company 1, Max salary is 21300. Employees in company 1 have taxes = 49%
For company 2, Max salary is 700. Employees in company 2 have taxes = 0%
For company 3, Max salary is 7777. Employees in company 3 have taxes = 24%
The salary after taxes = salary - (taxes percentage / 100) * salary
For example, Salary for Morninngcat (3, 15) after taxes = 7777 - 7777 * (24 / 100) = 7777 - 1866.48 = 5910.52, which is rounded to 5911.

思路

税率由公司内的最高工资决定，同一家公司的所有员工用同一个税率，不是看每个人自己的工资。

对每家公司算出最高工资 mx，并附在这家公司每个员工的那一行上。
根据 mx 确定税率：
mx < 1000：0%
1000 ≤ mx ≤ 10000：24%
mx > 10000：49%
税后工资 = salary × (1 - 税率)，四舍五入取整。

两个细节：

边界：恰好 1000 和恰好 10000 都属于 24%，所以判断顺序是"先看是否小于 1000，再看是否不超过 10000，其余为 49%"。
四舍五入的精度：用浮点数做乘法再取整，遇到恰好 .5 的情况可能因为精度误差而进位错误。MySQL 的小数字面量（如 0.24）是精确的 DECIMAL，没有这个问题。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     company_id,
   
4.     employee_id,
   
5.     employee_name,
   
6.     ROUND(
   
7.         salary * (1 - CASE
   
8.                           WHEN mx < 1000   THEN 0
   
9.                           WHEN mx <= 10000 THEN 0.24
   
10.                           ELSE 0.49
    
11.                       END)
    
12.     ) AS salary
    
13. FROM (
    
14.     SELECT
    
15.         *,
    
16.         MAX(salary) OVER (PARTITION BY company_id) AS mx
    
17.     FROM Salaries
    
18. ) t;

复制代码

1459. Rectangles Area
Medium
Topics
SQL Schema
Pandas Schema
Table: Points

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| x_value       | int     |
| y_value       | int     |
+---------------+---------+
id is the column with unique values for this table.
Each point is represented as a 2D coordinate (x_value, y_value).


Write a solution to report all possible axis-aligned rectangles with a non-zero area that can be formed by any two points from the Points table.

Each row in the result should contain three columns (p1, p2, area) where:

p1 and p2 are the id's of the two points that determine the opposite corners of a rectangle.
area is the area of the rectangle and must be non-zero.
Return the result table ordered by area in descending order. If there is a tie, order them by p1 in ascending order. If there is still a tie, order them by p2 in ascending order.

The result format is in the following table.



Example 1:


Input:
Points table:
+----------+-------------+-------------+
| id       | x_value     | y_value     |
+----------+-------------+-------------+
| 1        | 2           | 7           |
| 2        | 4           | 8           |
| 3        | 2           | 10          |
+----------+-------------+-------------+
Output:
+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 4           |
| 1        | 2           | 2           |
+----------+-------------+-------------+
Explanation:
The rectangle formed by p1 = 2 and p2 = 3 has an area equal to |4-2| * |8-10| = 4.
The rectangle formed by p1 = 1 and p2 = 2 has an area equal to |2-4| * |7-8| = 2.
Note that the rectangle formed by p1 = 1 and p2 = 3 is invalid because the area is 0.

思路

要找出所有由两个点作为对角顶点、边与坐标轴平行、面积不为 0 的矩形。

配对：两个点确定一个矩形，所以把 Points 和自己连接，枚举所有点对。
去重：点对 (1, 2) 和 (2, 1) 是同一个矩形，所以只保留 p1.id < p2.id，这样每对只出现一次，也排除了点和自己配对。
面积非零：两个点的 x 坐标不同并且 y 坐标也不同，矩形的宽和高才都不为 0。如果 x 相同，两点在一条竖线上，宽为 0。如果 y 相同，高为 0。
面积：|x1 - x2| × |y1 - y2|。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     p1.id AS p1,
   
4.     p2.id AS p2,
   
5.     ABS(p1.x_value - p2.x_value) * ABS(p1.y_value - p2.y_value) AS area
   
6. FROM Points p1
   
7. JOIN Points p2
   
8.     ON p1.id < p2.id
   
9.    AND p1.x_value <> p2.x_value
   
10.    AND p1.y_value <> p2.y_value
    
11. ORDER BY area DESC, p1 ASC, p2 ASC;

复制代码

1454. Active Users
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Accounts

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key (column with unique values) for this table.
This table contains the account id and the user name of each account.


Table: Logins

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| login_date    | date    |
+---------------+---------+
This table may contain duplicate rows.
This table contains the account id of the user who logged in and the login date. A user may log in multiple times in the day.


Active users are those who logged in to their accounts for five or more consecutive days.

Write a solution to find the id and the name of active users.

Return the result table ordered by id.

The result format is in the following example.



Example 1:

Input:
Accounts table:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+
Logins table:
+----+------------+
| 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 |
+----+------------+
Output:
+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
Explanation:
User Winston with id = 1 logged in 2 times only in 2 different days, so, Winston is not an active user.
User Jonathan with id = 7 logged in 7 times in 6 different days, five of them were consecutive days, so, Jonathan is an active user.


Follow up: Could you write a general solution if the active users are those who logged in to their accounts for n or more consecutive days?


思路

这是**连续区间（gaps and islands）**问题加一层去重。要找连续登录至少 5 天的用户，有两个细节：

同一天可能登录多次：Logins 允许重复行，示例里用户 7 在 06-02 登录了两次。按"天"算连续，所以要先对 (id, login_date) 去重，否则行号会虚增，连续性判断就错了。
连续的判断：对每个用户，把去重后的日期排序并编号 rn = 1, 2, 3, ...。日期每增加 1 天，行号也增加 1，所以 日期 - rn 保持不变。中间断一天，差值就变了。差值相同的日期属于同一段连续登录，某一段的天数 ≥ 5，这个用户就是活跃用户。

1. # Write your MySQL query statement below
   
2. WITH d AS (
   
3.     SELECT DISTINCT id, login_date
   
4.     FROM Logins
   
5. ),
   
6. g AS (
   
7.     SELECT
   
8.         id,
   
9.         login_date,
   
10.         DATE_SUB(
    
11.             login_date,
    
12.             INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY login_date) DAY
    
13.         ) AS grp
    
14.     FROM d
    
15. ),
    
16. active AS (
    
17.     SELECT DISTINCT id
    
18.     FROM g
    
19.     GROUP BY id, grp
    
20.     HAVING COUNT(*) >= 5
    
21. )
    
22. SELECT a.id, a.name
    
23. FROM Accounts a
    
24. JOIN active v ON a.id = v.id
    
25. ORDER BY a.id;

复制代码