📣 黑五价提前开抢|VIP一年通行证5折,蓝莓、Offer多多同步特惠
楼主: Myron2017
跳转到指定楼层
上一主题 下一主题
收起左侧

刷题记录帖子

🔗
 楼主| Myron2017 3 天前 | 只看该作者
全局:
1978. Employees Whose Manager Left the Company
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Employees

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| manager_id  | int      |
| salary      | int      |
+-------------+----------+
In SQL, employee_id is the primary key for this table.
This table contains information about the employees, their salary, and the ID of their manager. Some employees do not have a manager (manager_id is null).


Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_id set to the manager that left.

Return the result table ordered by employee_id.

The result format is in the following example.



Example 1:

Input:  
Employees table:
+-------------+-----------+------------+--------+
| employee_id | name      | manager_id | salary |
+-------------+-----------+------------+--------+
| 3           | Mila      | 9          | 60301  |
| 12          | Antonella | null       | 31000  |
| 13          | Emery     | null       | 67084  |
| 1           | Kalel     | 11         | 21241  |
| 9           | Mikaela   | null       | 50937  |
| 11          | Joziah    | 6          | 28485  |
+-------------+-----------+------------+--------+
Output:
+-------------+
| employee_id |
+-------------+
| 11          |
+-------------+

Explanation:
The employees with a salary less than $30000 are 1 (Kalel) and 11 (Joziah).
Kalel's manager is employee 11, who is still in the company (Joziah).
Joziah's manager is employee 6, who left the company because there is no row for employee 6 as it was deleted.
  1. # Write your MySQL query statement below
  2. SELECT e.employee_id
  3. FROM Employees e
  4. LEFT JOIN Employees m
  5.   ON e.manager_id = m.employee_id
  6. WHERE e.salary < 30000
  7.   AND e.manager_id IS NOT NULL
  8.   AND m.employee_id IS NULL
  9. ORDER BY e.employee_id;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 前天 09:53 | 只看该作者
全局:
2020. Number of Accounts That Did Not Stream
Medium
Topics
SQL Schema
Pandas Schema
Table: Subscriptions

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| start_date  | date |
| end_date    | date |
+-------------+------+
account_id is the primary key column for this table.
Each row of this table indicates the start and end dates of an account's subscription.
Note that always start_date < end_date.


Table: Streams

+-------------+------+
| Column Name | Type |
+-------------+------+
| session_id  | int  |
| account_id  | int  |
| stream_date | date |
+-------------+------+
session_id is the primary key column for this table.
account_id is a foreign key from the Subscriptions table.
Each row of this table contains information about the account and the date associated with a stream session.


Write an SQL query to report the number of accounts that bought a subscription in 2021 but did not have any stream session.

The query result format is in the following example.



Example 1:

Input:
Subscriptions table:
+------------+------------+------------+
| account_id | start_date | end_date   |
+------------+------------+------------+
| 9          | 2020-02-18 | 2021-10-30 |
| 3          | 2021-09-21 | 2021-11-13 |
| 11         | 2020-02-28 | 2020-08-18 |
| 13         | 2021-04-20 | 2021-09-22 |
| 4          | 2020-10-26 | 2021-05-08 |
| 5          | 2020-09-11 | 2021-01-17 |
+------------+------------+------------+
Streams table:
+------------+------------+-------------+
| session_id | account_id | stream_date |
+------------+------------+-------------+
| 14         | 9          | 2020-05-16  |
| 16         | 3          | 2021-10-27  |
| 18         | 11         | 2020-04-29  |
| 17         | 13         | 2021-08-08  |
| 19         | 4          | 2020-12-31  |
| 13         | 5          | 2021-01-05  |
+------------+------------+-------------+
Output:
+----------------+
| accounts_count |
+----------------+
| 2              |
+----------------+
Explanation: Users 4 and 9 did not stream in 2021.
User 11 did not subscribe in 2021.
  1. # Write your MySQL query statement below
  2. SELECT COUNT(*) AS accounts_count
  3. FROM Subscriptions s
  4. WHERE s.start_date <= '2021-12-31'
  5.   AND s.end_date   >= '2021-01-01'
  6.   AND NOT EXISTS (
  7.       SELECT 1
  8.       FROM Streams t
  9.       WHERE t.account_id = s.account_id
  10.         AND t.stream_date BETWEEN '2021-01-01' AND '2021-12-31'
  11.   );
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 前天 09:54 | 只看该作者
全局:
2026. Low-Quality Problems
Easy
Topics
SQL Schema
Pandas Schema
Table: Problems

+-------------+------+
| Column Name | Type |
+-------------+------+
| problem_id  | int  |
| likes       | int  |
| dislikes    | int  |
+-------------+------+
In SQL, problem_id is the primary key column for this table.
Each row of this table indicates the number of likes and dislikes for a LeetCode problem.


Find the IDs of the low-quality problems. A LeetCode problem is low-quality if the like percentage of the problem (number of likes divided by the total number of votes) is strictly less than 60%.

Return the result table ordered by problem_id in ascending order.

The result format is in the following example.



Example 1:

Input:
Problems table:
+------------+-------+----------+
| problem_id | likes | dislikes |
+------------+-------+----------+
| 6          | 1290  | 425      |
| 11         | 2677  | 8659     |
| 1          | 4446  | 2760     |
| 7          | 8569  | 6086     |
| 13         | 2050  | 4164     |
| 10         | 9002  | 7446     |
+------------+-------+----------+
Output:
+------------+
| problem_id |
+------------+
| 7          |
| 10         |
| 11         |
| 13         |
+------------+
Explanation: The like percentages are as follows:
- Problem 1: (4446 / (4446 + 2760)) * 100 = 61.69858%
- Problem 6: (1290 / (1290 + 425)) * 100 = 75.21866%
- Problem 7: (8569 / (8569 + 6086)) * 100 = 58.47151%
- Problem 10: (9002 / (9002 + 7446)) * 100 = 54.73006%
- Problem 11: (2677 / (2677 + 8659)) * 100 = 23.61503%
- Problem 13: (2050 / (2050 + 4164)) * 100 = 32.99002%
Problems 7, 10, 11, and 13 are low-quality problems because their like percentages are less than 60%.
  1. SELECT problem_id
  2. FROM Problems
  3. WHERE likes * 5 < 3 * (likes + dislikes)
  4. ORDER BY problem_id;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 前天 09:55 | 只看该作者
全局:
2041. Accepted Candidates From the Interviews
Medium
Topics
SQL Schema
Pandas Schema
Table: Candidates

+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| candidate_id | int      |
| name         | varchar  |
| years_of_exp | int      |
| interview_id | int      |
+--------------+----------+
candidate_id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of a candidate, their number of years of experience, and their interview ID.


Table: Rounds

+--------------+------+
| Column Name  | Type |
+--------------+------+
| interview_id | int  |
| round_id     | int  |
| score        | int  |
+--------------+------+
(interview_id, round_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the score of one round of an interview.


Write a solution to report the IDs of the candidates who have at least two years of experience and the sum of the score of their interview rounds is strictly greater than 15.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Candidates table:
+--------------+---------+--------------+--------------+
| candidate_id | name    | years_of_exp | interview_id |
+--------------+---------+--------------+--------------+
| 11           | Atticus | 1            | 101          |
| 9            | Ruben   | 6            | 104          |
| 6            | Aliza   | 10           | 109          |
| 8            | Alfredo | 0            | 107          |
+--------------+---------+--------------+--------------+
Rounds table:
+--------------+----------+-------+
| interview_id | round_id | score |
+--------------+----------+-------+
| 109          | 3        | 4     |
| 101          | 2        | 8     |
| 109          | 4        | 1     |
| 107          | 1        | 3     |
| 104          | 3        | 6     |
| 109          | 1        | 4     |
| 104          | 4        | 7     |
| 104          | 1        | 2     |
| 109          | 2        | 1     |
| 104          | 2        | 7     |
| 107          | 2        | 3     |
| 101          | 1        | 8     |
+--------------+----------+-------+
Output:
+--------------+
| candidate_id |
+--------------+
| 9            |
+--------------+
Explanation:
- Candidate 11: The total score is 16, and they have one year of experience. We do not include them in the result table because of their years of experience.
- Candidate 9: The total score is 22, and they have six years of experience. We include them in the result table.
- Candidate 6: The total score is 10, and they have ten years of experience. We do not include them in the result table because the score is not good enough.
- Candidate 8: The total score is 6, and they have zero years of experience. We do not include them in the result table because of their years of experience and the score.
  1. # Write your MySQL query statement below
  2. SELECT c.candidate_id
  3. FROM Candidates c
  4. JOIN Rounds r
  5.   ON r.interview_id = c.interview_id
  6. WHERE c.years_of_exp >= 2
  7. GROUP BY c.candidate_id
  8. HAVING SUM(r.score) > 15;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 前天 09:56 | 只看该作者
全局:
2051. The Category of Each Member in the Store
Medium
Topics
SQL Schema
Pandas Schema
Table: Members

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| member_id   | int     |
| name        | varchar |
+-------------+---------+
member_id is the column with unique values for this table.
Each row of this table indicates the name and the ID of a member.


Table: Visits

+-------------+------+
| Column Name | Type |
+-------------+------+
| visit_id    | int  |
| member_id   | int  |
| visit_date  | date |
+-------------+------+
visit_id is the column with unique values for this table.
member_id is a foreign key (reference column) to member_id from the Members table.
Each row of this table contains information about the date of a visit to the store and the member who visited it.


Table: Purchases

+----------------+------+
| Column Name    | Type |
+----------------+------+
| visit_id       | int  |
| charged_amount | int  |
+----------------+------+
visit_id is the column with unique values for this table.
visit_id is a foreign key (reference column) to visit_id from the Visits table.
Each row of this table contains information about the amount charged in a visit to the store.


A store wants to categorize its members. There are three tiers:

"Diamond": if the conversion rate is greater than or equal to 80.
"Gold": if the conversion rate is greater than or equal to 50 and less than 80.
"Silver": if the conversion rate is less than 50.
"Bronze": if the member never visited the store.
The conversion rate of a member is (100 * total number of purchases for the member) / total number of visits for the member.

Write a solution to report the id, the name, and the category of each member.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Members table:
+-----------+---------+
| member_id | name    |
+-----------+---------+
| 9         | Alice   |
| 11        | Bob     |
| 3         | Winston |
| 8         | Hercy   |
| 1         | Narihan |
+-----------+---------+
Visits table:
+----------+-----------+------------+
| visit_id | member_id | visit_date |
+----------+-----------+------------+
| 22       | 11        | 2021-10-28 |
| 16       | 11        | 2021-01-12 |
| 18       | 9         | 2021-12-10 |
| 19       | 3         | 2021-10-19 |
| 12       | 11        | 2021-03-01 |
| 17       | 8         | 2021-05-07 |
| 21       | 9         | 2021-05-12 |
+----------+-----------+------------+
Purchases table:
+----------+----------------+
| visit_id | charged_amount |
+----------+----------------+
| 12       | 2000           |
| 18       | 9000           |
| 17       | 7000           |
+----------+----------------+
Output:
+-----------+---------+----------+
| member_id | name    | category |
+-----------+---------+----------+
| 1         | Narihan | Bronze   |
| 3         | Winston | Silver   |
| 8         | Hercy   | Diamond  |
| 9         | Alice   | Gold     |
| 11        | Bob     | Silver   |
+-----------+---------+----------+
Explanation:
- User Narihan with id = 1 did not make any visits to the store. She gets a Bronze category.
- User Winston with id = 3 visited the store one time and did not purchase anything. The conversion rate = (100 * 0) / 1 = 0. He gets a Silver category.
- User Hercy with id = 8 visited the store one time and purchased one time. The conversion rate = (100 * 1) / 1 = 1. He gets a Diamond category.
- User Alice with id = 9 visited the store two times and purchased one time. The conversion rate = (100 * 1) / 2 = 50. She gets a Gold category.
- User Bob with id = 11 visited the store three times and purchased one time. The conversion rate = (100 * 1) / 3 = 33.33. He gets a Silver category.
  1. # Write your MySQL query statement below
  2. SELECT
  3.     m.member_id,
  4.     m.name,
  5.     CASE
  6.         WHEN COUNT(v.visit_id) = 0 THEN 'Bronze'
  7.         WHEN COUNT(p.visit_id) * 5 >= COUNT(v.visit_id) * 4 THEN 'Diamond'
  8.         WHEN COUNT(p.visit_id) * 2 >= COUNT(v.visit_id)     THEN 'Gold'
  9.         ELSE 'Silver'
  10.     END AS category
  11. FROM Members m
  12. LEFT JOIN Visits v
  13.   ON v.member_id = m.member_id
  14. LEFT JOIN Purchases p
  15.   ON p.visit_id = v.visit_id
  16. GROUP BY m.member_id, m.name;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 前天 09:57 | 只看该作者
全局:
2066. Account Balance
Medium
Topics
SQL Schema
Pandas Schema
Table: Transactions

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| day         | date |
| type        | ENUM |
| amount      | int  |
+-------------+------+
(account_id, day) is the primary key (combination of columns with unique values) for this table.
Each row contains information about one transaction, including the transaction type, the day it occurred on, and the amount.
type is an ENUM (category) of the type ('Deposit','Withdraw')


Write a solution to report the balance of each user after each transaction. You may assume that the balance of each account before any transaction is 0 and that the balance will never be below 0 at any moment.

Return the result table in ascending order by account_id, then by day in case of a tie.

The result format is in the following example.



Example 1:

Input:
Transactions table:
+------------+------------+----------+--------+
| account_id | day        | type     | amount |
+------------+------------+----------+--------+
| 1          | 2021-11-07 | Deposit  | 2000   |
| 1          | 2021-11-09 | Withdraw | 1000   |
| 1          | 2021-11-11 | Deposit  | 3000   |
| 2          | 2021-12-07 | Deposit  | 7000   |
| 2          | 2021-12-12 | Withdraw | 7000   |
+------------+------------+----------+--------+
Output:
+------------+------------+---------+
| account_id | day        | balance |
+------------+------------+---------+
| 1          | 2021-11-07 | 2000    |
| 1          | 2021-11-09 | 1000    |
| 1          | 2021-11-11 | 4000    |
| 2          | 2021-12-07 | 7000    |
| 2          | 2021-12-12 | 0       |
+------------+------------+---------+
Explanation:
Account 1:
- Initial balance is 0.
- 2021-11-07 --> deposit 2000. Balance is 0 + 2000 = 2000.
- 2021-11-09 --> withdraw 1000. Balance is 2000 - 1000 = 1000.
- 2021-11-11 --> deposit 3000. Balance is 1000 + 3000 = 4000.
Account 2:
- Initial balance is 0.
- 2021-12-07 --> deposit 7000. Balance is 0 + 7000 = 7000.
- 2021-12-12 --> withdraw 7000. Balance is 7000 - 7000 = 0.
  1. # Write your MySQL query statement below
  2. SELECT
  3.     account_id,
  4.     day,
  5.     SUM(CASE WHEN type = 'Deposit' THEN amount ELSE -amount END) OVER (
  6.         PARTITION BY account_id
  7.         ORDER BY day
  8.         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  9.     ) AS balance
  10. FROM Transactions
  11. ORDER BY account_id, day;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:49 | 只看该作者
全局:
2072. The Winner University
Easy
Topics
SQL Schema
Pandas Schema
Table: NewYork

+--------------------------------------------------+--------------------------------------------------+
| Column Name | Type |
+--------------------------------------------------+--------------------------------------------------+
| student_id | int |
| score | int |
+--------------------------------------------------+--------------------------------------------------+
In SQL, student_id is the primary key for this table.
Each row contains information about the score of one student from New York University in an exam.

Table: California

+--------------------------------------------------+--------------------------------------------------+
| Column Name | Type |
+--------------------------------------------------+--------------------------------------------------+
| student_id | int |
| score | int |
+--------------------------------------------------+--------------------------------------------------+
In SQL, student_id is the primary key for this table.
Each row contains information about the score of one student from California University in an exam.

There is a competition between New York University and California University. The competition is held between the same number of students from both universities. The university that has more excellent students wins the competition. If the two universities have the same number of excellent students, the competition ends in a draw.

An excellent student is a student that scored 90% or more in the exam.

Return:

"New York University" if New York University wins the competition.
"California University" if California University wins the competition.
"No Winner" if the competition ends in a draw.
The result format is in the following example.

Example 1:

Input:
NewYork table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 1 | 90 |
| 2 | 87 |
+--------------------------------------------------+--------------------------------------------------+
California table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 2 | 89 |
| 3 | 88 |
+--------------------------------------------------+--------------------------------------------------+
Output:
+--------------------------------------------------+
| winner |
+--------------------------------------------------+
| New York University |
+--------------------------------------------------+
Explanation:
New York University has 1 excellent student, and California University has 0 excellent students.
Example 2:

Input:
NewYork table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 1 | 89 |
| 2 | 88 |
+--------------------------------------------------+--------------------------------------------------+
California table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 2 | 90 |
| 3 | 87 |
+--------------------------------------------------+--------------------------------------------------+
Output:
+--------------------------------------------------+
| winner |
+--------------------------------------------------+
| California University |
+--------------------------------------------------+
Explanation:
New York University has 0 excellent students, and California University has 1 excellent student.
Example 3:

Input:
NewYork table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 1 | 89 |
| 2 | 90 |
+--------------------------------------------------+--------------------------------------------------+
California table:
+--------------------------------------------------+--------------------------------------------------+
| student_id | score |
+--------------------------------------------------+--------------------------------------------------+
| 2 | 87 |
| 3 | 99 |
+--------------------------------------------------+--------------------------------------------------+
Output:
+--------------------------------------------------+
| winner |
+--------------------------------------------------+
| No Winner |
+--------------------------------------------------+
Explanation:
Both New York University and California University have 1 excellent student.
  1. Write your MySQL query statement below
  2. SELECT CASE WHEN (SELECT COUNT() FROM NewYork WHERE score >= 90) > (SELECT COUNT() FROM California WHERE score >= 90) THEN 'New York University' WHEN (SELECT COUNT() FROM NewYork WHERE score >= 90) < (SELECT COUNT() FROM California WHERE score >= 90) THEN 'California University' ELSE 'No Winner' END AS winner;
  3.    
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:52 | 只看该作者
全局:
2082. The Number of Rich Customers
Easy
Topics
SQL Schema
Pandas Schema
Table: Store

+-------------+------+
| Column Name | Type |
+-------------+------+
| bill_id     | int  |
| customer_id | int  |
| amount      | int  |
+-------------+------+
bill_id is the primary key (column with unique values) for this table.
Each row contains information about the amount of one bill and the customer associated with it.


Write a solution to report the number of customers who had at least one bill with an amount strictly greater than 500.

The result format is in the following example.



Example 1:

Input:
Store table:
+---------+-------------+--------+
| bill_id | customer_id | amount |
+---------+-------------+--------+
| 6       | 1           | 549    |
| 8       | 1           | 834    |
| 4       | 2           | 394    |
| 11      | 3           | 657    |
| 13      | 3           | 257    |
+---------+-------------+--------+
Output:
+------------+
| rich_count |
+------------+
| 2          |
+------------+
Explanation:
Customer 1 has two bills with amounts strictly greater than 500.
Customer 2 does not have any bills with an amount strictly greater than 500.
Customer 3 has one bill with an amount strictly greater than 500.
  1. SELECT COUNT(DISTINCT customer_id) AS rich_count
  2. FROM Store
  3. WHERE amount > 500;
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:53 | 只看该作者
全局:
2084. Drop Type 1 Orders for Customers With Type 0 Orders
Medium
Topics
SQL Schema
Pandas Schema
Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  |
| customer_id | int  |
| order_type  | int  |
+-------------+------+
order_id is the column with unique values for this table.
Each row of this table indicates the ID of an order, the ID of the customer who ordered it, and the order type.
The orders could be of type 0 or type 1.


Write a solution to report all the orders based on the following criteria:

If a customer has at least one order of type 0, do not report any order of type 1 from that customer.
Otherwise, report all the orders of the customer.
Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Orders table:
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 12       | 2           | 1          |
| 21       | 3           | 1          |
| 22       | 3           | 0          |
| 31       | 4           | 1          |
| 32       | 4           | 1          |
+----------+-------------+------------+
Output:
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 31       | 4           | 1          |
| 32       | 4           | 1          |
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 22       | 3           | 0          |
+----------+-------------+------------+
Explanation:
Customer 1 has two orders of type 0. We return both of them.
Customer 2 has one order of type 0 and one order of type 1. We only return the order of type 0.
Customer 3 has one order of type 0 and one order of type 1. We only return the order of type 0.
Customer 4 has two orders of type 1. We return both of them.
  1. SELECT order_id, customer_id, order_type
  2. FROM Orders o
  3. WHERE order_type = 0
  4.    OR NOT EXISTS (
  5.        SELECT 1
  6.        FROM Orders
  7.        WHERE customer_id = o.customer_id
  8.          AND order_type = 0
  9.    );
复制代码
回复

使用道具 举报

🔗
 楼主| Myron2017 昨天 10:54 | 只看该作者
全局:
2112. The Airport With the Most Traffic
Medium
Topics
SQL Schema
Pandas Schema
Table: Flights

+-------------------+------+
| Column Name       | Type |
+-------------------+------+
| departure_airport | int  |
| arrival_airport   | int  |
| flights_count     | int  |
+-------------------+------+
(departure_airport, arrival_airport) is the primary key column (combination of columns with unique values) for this table.
Each row of this table indicates that there were flights_count flights that departed from departure_airport and arrived at arrival_airport.


Write a solution to report the ID of the airport with the most traffic. The airport with the most traffic is the airport that has the largest total number of flights that either departed from or arrived at the airport. If there is more than one airport with the most traffic, report them all.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Flights table:
+-------------------+-----------------+---------------+
| departure_airport | arrival_airport | flights_count |
+-------------------+-----------------+---------------+
| 1                 | 2               | 4             |
| 2                 | 1               | 5             |
| 2                 | 4               | 5             |
+-------------------+-----------------+---------------+
Output:
+------------+
| airport_id |
+------------+
| 2          |
+------------+
Explanation:
Airport 1 was engaged with 9 flights (4 departures, 5 arrivals).
Airport 2 was engaged with 14 flights (10 departures, 4 arrivals).
Airport 4 was engaged with 5 flights (5 arrivals).
The airport with the most traffic is airport 2.
Example 2:

Input:
Flights table:
+-------------------+-----------------+---------------+
| departure_airport | arrival_airport | flights_count |
+-------------------+-----------------+---------------+
| 1                 | 2               | 4             |
| 2                 | 1               | 5             |
| 3                 | 4               | 5             |
| 4                 | 3               | 4             |
| 5                 | 6               | 7             |
+-------------------+-----------------+---------------+
Output:
+------------+
| airport_id |
+------------+
| 1          |
| 2          |
| 3          |
| 4          |
+------------+
Explanation:
Airport 1 was engaged with 9 flights (4 departures, 5 arrivals).
Airport 2 was engaged with 9 flights (5 departures, 4 arrivals).
Airport 3 was engaged with 9 flights (5 departures, 4 arrivals).
Airport 4 was engaged with 9 flights (4 departures, 5 arrivals).
Airport 5 was engaged with 7 flights (7 departures).
Airport 6 was engaged with 7 flights (7 arrivals).
The airports with the most traffic are airports 1, 2, 3, and 4.
  1. # Write your MySQL query statement below
  2. WITH T AS (
  3.     SELECT departure_airport AS airport_id, flights_count FROM Flights
  4.     UNION ALL
  5.     SELECT arrival_airport   AS airport_id, flights_count FROM Flights
  6. ),
  7. S AS (
  8.     SELECT
  9.         airport_id,
  10.         SUM(flights_count) AS total,
  11.         RANK() OVER (ORDER BY SUM(flights_count) DESC) AS rnk
  12.     FROM T
  13.     GROUP BY airport_id
  14. )
  15. SELECT airport_id
  16. FROM S
  17. WHERE rnk = 1;
复制代码
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号
隐私提醒:
  • ☑ 禁止发布广告,拉群,贴个人联系方式:找人请去🔗同学同事飞友,拉群请去🔗拉群结伴,广告请去🔗跳蚤市场,和 🔗租房广告|找室友
  • ☑ 论坛内容在发帖 30 分钟内可以编辑,过后则不能删帖。为防止被骚扰甚至人肉,不要公开留微信等联系方式,如有需求请以论坛私信方式发送。
  • ☑ 干货版块可免费使用 🔗超级匿名:面经(美国面经、中国面经、数科面经、PM面经),抖包袱(美国、中国)和录取汇报、定位选校版
  • ☑ 查阅全站 🔗各种匿名方法

本版积分规则

>
快速回复 返回顶部 返回列表