刷题记录帖子

1445. Apples & Oranges
Medium
Topics
SQL Schema
Pandas Schema
Table: Sales

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| sale_date     | date    |
| fruit         | enum    |
| sold_num      | int     |
+---------------+---------+
(sale_date, fruit) is the primary key (combination of columns with unique values) of this table.
This table contains the sales of "apples" and "oranges" sold each day.


Write a solution to report the difference between the number of apples and oranges sold each day.

Return the result table ordered by sale_date.

The result format is in the following example.



Example 1:

Input:
Sales table:
+------------+------------+-------------+
| sale_date  | fruit      | sold_num    |
+------------+------------+-------------+
| 2020-05-01 | apples     | 10          |
| 2020-05-01 | oranges    | 8           |
| 2020-05-02 | apples     | 15          |
| 2020-05-02 | oranges    | 15          |
| 2020-05-03 | apples     | 20          |
| 2020-05-03 | oranges    | 0           |
| 2020-05-04 | apples     | 15          |
| 2020-05-04 | oranges    | 16          |
+------------+------------+-------------+
Output:
+------------+--------------+
| sale_date  | diff         |
+------------+--------------+
| 2020-05-01 | 2            |
| 2020-05-02 | 0            |
| 2020-05-03 | 20           |
| 2020-05-04 | -1           |
+------------+--------------+
Explanation:
Day 2020-05-01, 10 apples and 8 oranges were sold (Difference  10 - 8 = 2).
Day 2020-05-02, 15 apples and 15 oranges were sold (Difference 15 - 15 = 0).
Day 2020-05-03, 20 apples and 0 oranges were sold (Difference 20 - 0 = 20).
Day 2020-05-04, 15 apples and 16 oranges were sold (Difference 15 - 16 = -1).

思路

每天恰好有两行：一行是苹果，一行是橘子。要的是苹果数 - 橘子数。

做法是把每一行变成对差值的"贡献值"：苹果记为正，橘子记为负，再按日期求和。这和 1393（Capital Gain/Loss）是同一个技巧：Σ(正的部分) - Σ(负的部分)。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     sale_date,
   
4.     SUM(CASE WHEN fruit = 'apples' THEN sold_num ELSE -sold_num END) AS diff
   
5. FROM Sales
   
6. GROUP BY sale_date
   
7. ORDER BY sale_date;

复制代码

1479. Sales by Day of the Week
Hard
Topics
SQL Schema
Pandas Schema
Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    |
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(ordered_id, item_id) is the primary key (combination of columns with unique values) for this table.
This table contains information on the orders placed.
order_date is the date item_id was ordered by the customer with id customer_id.


Table: Items

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id is the primary key (column with unique values) for this table.
item_name is the name of the item.
item_category is the category of the item.


You are the business owner and would like to obtain a sales report for category items and the day of the week.

Write a solution to report how many units in each category have been ordered on each day of the week.

Return the result table ordered by category.

The result format is in the following example.



Example 1:

Input:
Orders table:
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 1          | 1            | 2020-06-01  | 1            | 10          |
| 2          | 1            | 2020-06-08  | 2            | 10          |
| 3          | 2            | 2020-06-02  | 1            | 5           |
| 4          | 3            | 2020-06-03  | 3            | 5           |
| 5          | 4            | 2020-06-04  | 4            | 1           |
| 6          | 4            | 2020-06-05  | 5            | 5           |
| 7          | 5            | 2020-06-05  | 1            | 10          |
| 8          | 5            | 2020-06-14  | 4            | 5           |
| 9          | 5            | 2020-06-21  | 3            | 5           |
+------------+--------------+-------------+--------------+-------------+
Items table:
+------------+----------------+---------------+
| item_id    | item_name      | item_category |
+------------+----------------+---------------+
| 1          | LC Alg. Book   | Book          |
| 2          | LC DB. Book    | Book          |
| 3          | LC SmarthPhone | Phone         |
| 4          | LC Phone 2020  | Phone         |
| 5          | LC SmartGlass  | Glasses       |
| 6          | LC T-Shirt XL  | T-Shirt       |
+------------+----------------+---------------+
Output:
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
Explanation:
On Monday (2020-06-01, 2020-06-08) were sold a total of 20 units (10 + 10) in the category Book (ids: 1, 2).
On Tuesday (2020-06-02) were sold a total of 5 units in the category Book (ids: 1, 2).
On Wednesday (2020-06-03) were sold a total of 5 units in the category Phone (ids: 3, 4).
On Thursday (2020-06-04) were sold a total of 1 unit in the category Phone (ids: 3, 4).
On Friday (2020-06-05) were sold 10 units in the category Book (ids: 1, 2) and 5 units in Glasses (ids: 5).
On Saturday there are no items sold.
On Sunday (2020-06-14, 2020-06-21) were sold a total of 10 units (5 +5) in the category Phone (ids: 3, 4).
There are no sales of T-shirts.

思路

要做一张行是类别、列是星期一到星期日的汇总表，每个格子是该类别在该星期几卖出的总件数。这是典型的行转列（pivot），用条件聚合实现：

Orders 里只有 item_id，类别在 Items 里，所以要连接两张表。
同一个类别下有多个商品（如 Book 有商品 1 和 2），所以要按类别分组，不能按商品分组。
每个星期几一列，用 CASE WHEN 判断订单日期是星期几，是就取 quantity，否则取 0，再求和。

最容易错的地方是：没有任何销售的类别也要输出，如 T-Shirt 的七列全是 0。所以要以 Items 为主表做 LEFT JOIN，不能以 Orders 为主表，也不能用 INNER JOIN。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     i.item_category AS Category,
   
4.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 2 THEN o.quantity ELSE 0 END) AS Monday,
   
5.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 3 THEN o.quantity ELSE 0 END) AS Tuesday,
   
6.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 4 THEN o.quantity ELSE 0 END) AS Wednesday,
   
7.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 5 THEN o.quantity ELSE 0 END) AS Thursday,
   
8.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 6 THEN o.quantity ELSE 0 END) AS Friday,
   
9.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 7 THEN o.quantity ELSE 0 END) AS Saturday,
   
10.     SUM(CASE WHEN DAYOFWEEK(o.order_date) = 1 THEN o.quantity ELSE 0 END) AS Sunday
    
11. FROM Items i
    
12. LEFT JOIN Orders o
    
13.     ON i.item_id = o.item_id
    
14. GROUP BY i.item_category
    
15. ORDER BY i.item_category;

复制代码

1484. Group Sold Products By The Date
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table Activities:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each row of this table contains the product name and the date it was sold in a market.


Write a solution to find for each date the number of different products sold and their names.

The sold products names for each date should be sorted lexicographically.

Return the result table ordered by sell_date.

The result format is in the following example.



Example 1:

Input:
Activities table:
+------------+------------+
| sell_date  | product     |
+------------+------------+
| 2020-05-30 | Headphone  |
| 2020-06-01 | Pencil     |
| 2020-06-02 | Mask       |
| 2020-05-30 | Basketball |
| 2020-06-01 | Bible      |
| 2020-06-02 | Mask       |
| 2020-05-30 | T-Shirt    |
+------------+------------+
Output:
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
Explanation:
For 2020-05-30, Sold items were (Headphone, Basketball, T-shirt), we sort them lexicographically and separate them by a comma.
For 2020-06-01, Sold items were (Pencil, Bible), we sort them lexicographically and separate them by a comma.
For 2020-06-02, the Sold item is (Mask), we just return it.

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     sell_date,
   
4.     COUNT(DISTINCT product) AS num_sold,
   
5.     GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS products
   
6. FROM Activities
   
7. GROUP BY sell_date
   
8. ORDER BY sell_date;

复制代码

1495. Friendly Movies Streamed Last Month
Easy
Topics
SQL Schema
Pandas Schema
Table: TVProgram

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| program_date  | date    |
| content_id    | int     |
| channel       | varchar |
+---------------+---------+
(program_date, content_id) is the primary key (combination of columns with unique values) for this table.
This table contains information of the programs on the TV.
content_id is the id of the program in some channel on the TV.


Table: Content

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| content_id       | varchar |
| title            | varchar |
| Kids_content     | enum    |
| content_type     | varchar |
+------------------+---------+
content_id is the primary key (column with unique values) for this table.
Kids_content is an ENUM (category) of types ('Y', 'N') where:
'Y' means is content for kids otherwise 'N' is not content for kids.
content_type is the category of the content as movies, series, etc.


Write a solution to report the distinct titles of the kid-friendly movies streamed in June 2020.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
TVProgram table:
+--------------------+--------------+-------------+
| program_date       | content_id   | channel     |
+--------------------+--------------+-------------+
| 2020-06-10 08:00   | 1            | LC-Channel  |
| 2020-05-11 12:00   | 2            | LC-Channel  |
| 2020-05-12 12:00   | 3            | LC-Channel  |
| 2020-05-13 14:00   | 4            | Disney Ch   |
| 2020-06-18 14:00   | 4            | Disney Ch   |
| 2020-07-15 16:00   | 5            | Disney Ch   |
+--------------------+--------------+-------------+
Content table:
+------------+----------------+---------------+---------------+
| content_id | title          | Kids_content  | content_type  |
+------------+----------------+---------------+---------------+
| 1          | Leetcode Movie | N             | Movies        |
| 2          | Alg. for Kids  | Y             | Series        |
| 3          | Database Sols  | N             | Series        |
| 4          | Aladdin        | Y             | Movies        |
| 5          | Cinderella     | Y             | Movies        |
+------------+----------------+---------------+---------------+
Output:
+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+
Explanation:
"Leetcode Movie" is not a content for kids.
"Alg. for Kids" is not a movie.
"Database Sols" is not a movie
"Alladin" is a movie, content for kids and was streamed in June 2020.
"Cinderella" was not streamed in June 2020.

1. # Write your MySQL query statement below
   
2. SELECT DISTINCT c.title
   
3. FROM TVProgram t
   
4. JOIN Content c
   
5.     ON t.content_id = c.content_id
   
6. WHERE c.Kids_content = 'Y'
   
7.   AND c.content_type = 'Movies'
   
8.   AND t.program_date >= '2020-06-01'
   
9.   AND t.program_date <  '2020-07-01';

复制代码

1501. Countries You Can Safely Invest In
Medium
Topics
SQL Schema
Pandas Schema
Table Person:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| name           | varchar |
| phone_number   | varchar |
+----------------+---------+
id is the column of unique values for this table.
Each row of this table contains the name of a person and their phone number.
Phone number will be in the form 'xxx-yyyyyyy' where xxx is the country code (3 characters) and yyyyyyy is the phone number (7 characters) where x and y are digits. Both can contain leading zeros.


Table Country:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| name           | varchar |
| country_code   | varchar |
+----------------+---------+
country_code is the column of unique values for this table.
Each row of this table contains the country name and its code. country_code will be in the form 'xxx' where x is digits.


Table Calls:

+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id   | int  |
| callee_id   | int  |
| duration    | int  |
+-------------+------+
This table may contain duplicate rows.
Each row of this table contains the caller id, callee id and the duration of the call in minutes. caller_id != callee_id


A telecommunications company wants to invest in new countries. The company intends to invest in the countries where the average call duration of the calls in this country is strictly greater than the global average call duration.

Write a solution to find the countries where this company can invest.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Person table:
+----+----------+--------------+
| id | name     | phone_number |
+----+----------+--------------+
| 3  | Jonathan | 051-1234567  |
| 12 | Elvis    | 051-7654321  |
| 1  | Moncef   | 212-1234567  |
| 2  | Maroua   | 212-6523651  |
| 7  | Meir     | 972-1234567  |
| 9  | Rachel   | 972-0011100  |
+----+----------+--------------+
Country table:
+----------+--------------+
| name     | country_code |
+----------+--------------+
| Peru     | 051          |
| Israel   | 972          |
| Morocco  | 212          |
| Germany  | 049          |
| Ethiopia | 251          |
+----------+--------------+
Calls table:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1         | 9         | 33       |
| 2         | 9         | 4        |
| 1         | 2         | 59       |
| 3         | 12        | 102      |
| 3         | 12        | 330      |
| 12        | 3         | 5        |
| 7         | 9         | 13       |
| 7         | 1         | 3        |
| 9         | 7         | 1        |
| 1         | 7         | 7        |
+-----------+-----------+----------+
Output:
+----------+
| country  |
+----------+
| Peru     |
+----------+
Explanation:
The average call duration for Peru is (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
The average call duration for Israel is (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
The average call duration for Morocco is (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000
Global call duration average = (2 * (33 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
Since Peru is the only country where the average call duration is greater than the global average, it is the only recommended country.

1. # Write your MySQL query statement below
   
2. WITH all_calls AS (
   
3.     SELECT caller_id AS id, duration FROM Calls
   
4.     UNION ALL
   
5.     SELECT callee_id AS id, duration FROM Calls
   
6. )
   
7. SELECT c.name AS country
   
8. FROM all_calls a
   
9. JOIN Person p
   
10.     ON a.id = p.id
    
11. JOIN Country c
    
12.     ON c.country_code = LEFT(p.phone_number, 3)
    
13. GROUP BY c.name
    
14. HAVING AVG(a.duration) > (SELECT AVG(duration) FROM Calls);

复制代码

1511. Customer Order Frequency
Easy
Topics
SQL Schema
Pandas Schema
Table: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| country       | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
This table contains information about the customers in the company.


Table: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| description   | varchar |
| price         | int     |
+---------------+---------+
product_id is the column with unique values for this table.
This table contains information on the products in the company.
price is the product cost.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_id    | int     |
| order_date    | date    |
| quantity      | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information on customer orders.
customer_id is the id of the customer who bought "quantity" products with id "product_id".
Order_date is the date in format ('YYYY-MM-DD') when the order was shipped.


Write a solution to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Customers table:
+--------------+-----------+-------------+
| customer_id  | name      | country     |
+--------------+-----------+-------------+
| 1            | Winston   | USA         |
| 2            | Jonathan  | Peru        |
| 3            | Moustafa  | Egypt       |
+--------------+-----------+-------------+
Product table:
+--------------+-------------+-------------+
| product_id   | description | price       |
+--------------+-------------+-------------+
| 10           | LC Phone    | 300         |
| 20           | LC T-Shirt  | 10          |
| 30           | LC Book     | 45          |
| 40           | LC Keychain | 2           |
+--------------+-------------+-------------+
Orders table:
+--------------+-------------+-------------+-------------+-----------+
| order_id     | customer_id | product_id  | order_date  | quantity  |
+--------------+-------------+-------------+-------------+-----------+
| 1            | 1           | 10          | 2020-06-10  | 1         |
| 2            | 1           | 20          | 2020-07-01  | 1         |
| 3            | 1           | 30          | 2020-07-08  | 2         |
| 4            | 2           | 10          | 2020-06-15  | 2         |
| 5            | 2           | 40          | 2020-07-01  | 10        |
| 6            | 3           | 20          | 2020-06-24  | 2         |
| 7            | 3           | 30          | 2020-06-25  | 2         |
| 9            | 3           | 30          | 2020-05-08  | 3         |
+--------------+-------------+-------------+-------------+-----------+
Output:
+--------------+------------+
| customer_id  | name       |  
+--------------+------------+
| 1            | Winston    |
+--------------+------------+
Explanation:
Winston spent $300 (300 * 1) in June and $100 ( 10 * 1 + 45 * 2) in July 2020.
Jonathan spent $600 (300 * 2) in June and $20 ( 2 * 10) in July 2020.
Moustafa spent $110 (10 * 2 + 45 * 2) in June and $0 in July 2020.

思路

要找 6 月和 7 月每个月的消费额都不少于 100 美元的顾客。要注意的是两个月各自都要达标，而不是两个月合计达标。

算每笔订单的金额：订单金额 = 商品单价 × 数量。单价在 Product 里，所以要连接 Product。
只看 6、7 月：先把日期限定在 2020 年 6 月 1 日到 7 月 31 日。
按顾客分组，分别累计两个月的金额：用条件聚合，一个算 6 月，一个算 7 月。
用 HAVING 要求两个月都 ≥ 100。

用 INNER JOIN 就够了：某个月一笔订单都没有的顾客，那个月的消费是 0，本来就不达标。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     c.customer_id,
   
4.     c.name
   
5. FROM Orders o
   
6. JOIN Product p
   
7.     ON o.product_id = p.product_id
   
8. JOIN Customers c
   
9.     ON o.customer_id = c.customer_id
   
10. WHERE o.order_date >= '2020-06-01'
    
11.   AND o.order_date <  '2020-08-01'
    
12. GROUP BY c.customer_id, c.name
    
13. HAVING SUM(CASE WHEN o.order_date <  '2020-07-01'
    
14.                 THEN p.price * o.quantity ELSE 0 END) >= 100
    
15.    AND SUM(CASE WHEN o.order_date >= '2020-07-01'
    
16.                 THEN p.price * o.quantity ELSE 0 END) >= 100;

复制代码

1527. Patients With a Condition
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id is the primary key (column with unique values) for this table.
'conditions' contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.


Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
Output:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.

1527. Patients With a Condition
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
patient_id is the primary key (column with unique values) for this table.
'conditions' contains 0 or more code separated by spaces.
This table contains information of the patients in the hospital.


Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Patients table:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
Output:
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
+------------+--------------+--------------+
Explanation: Bob and George both have a condition that starts with DIAB1.

思路

conditions 是由空格分隔的若干个疾病代码（可能为空），要找至少有一个代码以 DIAB1 开头的患者。

关键是"以 DIAB1 开头"指的是某一个代码的开头，不是整个字符串的开头，也不是字符串里任意位置出现 DIAB1。这个代码在字符串里只有两种位置：

第一个代码：它在整个字符串的最前面，匹配 'DIAB1%'。
后面的代码：它前面一定有一个空格分隔，匹配 '% DIAB1%'。

两种情况用 OR 连接。

不能直接写 LIKE '%DIAB1%'。如果有个代码是 ADIAB100，DIAB1 出现在它的中间，不是开头，却会被误判为匹配。多了前面那个空格，才能保证 DIAB1 是一个代码的起点。

1. # Write your MySQL query statement below
   
2. SELECT patient_id, patient_name, conditions
   
3. FROM Patients
   
4. WHERE conditions LIKE 'DIAB1%'
   
5.    OR conditions LIKE '% DIAB1%';

复制代码

1517. Find Users With Valid E-Mails
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id is the primary key (column with unique values) for this table.
This table contains information of the users signed up in a website. Some e-mails are invalid.


Write a solution to find the users who have valid emails.

A valid e-mail has a prefix name and a domain where:

The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.
The domain must be exactly '@leetcode.com' in lowercase.
Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Users table:
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 2       | Jonathan  | jonathanisgreat         |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
| 5       | Marwan    | quarz#2020@leetcode.com |
| 6       | David     | david69@gmail.com       |
| 7       | Shapiro   | .shapo@leetcode.com     |
+---------+-----------+-------------------------+
Output:
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
Explanation:
The mail of user 2 does not have a domain.
The mail of user 5 has the # sign which is not allowed.
The mail of user 6 does not have the leetcode domain.
The mail of user 7 starts with a period.

思路

合法邮箱由两部分组成：

前缀：只能包含字母（大小写均可）、数字、下划线 _、点 .、短横线 -，并且必须以字母开头。
域名：必须恰好是小写的 @leetcode.com。

这类"格式校验"最直接的做法是用正则表达式，把整个字符串从头到尾描述一遍：

^[A-Za-z][A-Za-z0-9_.-]*@leetcode\.com$
^ 和 $：匹配整个字符串，不能只匹配其中一段。
[A-Za-z]：第一个字符必须是字母。
[A-Za-z0-9_.-]*：后面可以有零个或多个合法字符，所以前缀只有一个字母也合法。
@leetcode\.com：域名固定。. 在正则里表示"任意字符"，要写成 \. 才是字面的点。

有两个容易错的地方：

MySQL 的 REGEXP 默认不区分大小写（取决于排序规则）。如果直接用 REGEXP，@LEETCODE.COM 也会被判为合法，而题目要求域名必须是小写，所以要用区分大小写的写法。
域名里的点要转义。不转义的话，@leetcodeXcom 也会被匹配上。

1. # Write your MySQL query statement below
   
2. SELECT user_id, name, mail
   
3. FROM Users
   
4. WHERE REGEXP_LIKE(mail, '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com, 'c');

复制代码

1565. Unique Orders and Customers Per Month
Easy
Topics
SQL Schema
Pandas Schema
Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| invoice       | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information about the orders made by customer_id.


Write a solution to find the number of unique orders and the number of unique customers with invoices > $20 for each different month.

Return the result table sorted in any order.

The result format is in the following example.



Example 1:

Input:
Orders table:
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | invoice    |
+----------+------------+-------------+------------+
| 1        | 2020-09-15 | 1           | 30         |
| 2        | 2020-09-17 | 2           | 90         |
| 3        | 2020-10-06 | 3           | 20         |
| 4        | 2020-10-20 | 3           | 21         |
| 5        | 2020-11-10 | 1           | 10         |
| 6        | 2020-11-21 | 2           | 15         |
| 7        | 2020-12-01 | 4           | 55         |
| 8        | 2020-12-03 | 4           | 77         |
| 9        | 2021-01-07 | 3           | 31         |
| 10       | 2021-01-15 | 2           | 20         |
+----------+------------+-------------+------------+
Output:
+---------+-------------+----------------+
| month   | order_count | customer_count |
+---------+-------------+----------------+
| 2020-09 | 2           | 2              |
| 2020-10 | 1           | 1              |
| 2020-12 | 2           | 1              |
| 2021-01 | 1           | 1              |
+---------+-------------+----------------+
Explanation:
In September 2020 we have two orders from 2 different customers with invoices > $20.
In October 2020 we have two orders from 1 customer, and only one of the two orders has invoice > $20.
In November 2020 we have two orders from 2 different customers but invoices < $20, so we don't include that month.
In December 2020 we have two orders from 1 customer both with invoices > $20.
In January 2021 we have two orders from 2 different customers, but only one of them with invoice > $20.

思路

先筛选，再按月份分组，每组统计两个"不重复"的数量：

筛选：只保留 invoice > 20 的订单。题目是"大于 $20"，所以恰好 20 的订单（订单 3 和订单 10）要排除。
按月分组：把 order_date 转成 YYYY-MM 格式再分组，同一个月的订单归为一组。
统计：
order_count：不重复的订单数，用 COUNT(DISTINCT order_id)。
customer_count：不重复的顾客数，用 COUNT(DISTINCT customer_id)。同一位顾客一个月里下了多单，只算一个人，如 12 月顾客 4 下了 2 单。

日期过滤要写在 WHERE 里，也就是分组之前。如果某个月的订单金额都不超过 20（如 11 月），过滤后这个月没有任何行，就不会形成分组，结果里自然不会出现，不需要额外处理。

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     DATE_FORMAT(order_date, '%Y-%m') AS month,
   
4.     COUNT(DISTINCT order_id)         AS order_count,
   
5.     COUNT(DISTINCT customer_id)      AS customer_count
   
6. FROM Orders
   
7. WHERE invoice > 20
   
8. GROUP BY DATE_FORMAT(order_date, '%Y-%m');# Write your MySQL query statement below
   
9. SELECT
   
10.     DATE_FORMAT(order_date, '%Y-%m') AS month,
    
11.     COUNT(DISTINCT order_id)         AS order_count,
    
12.     COUNT(DISTINCT customer_id)      AS customer_count
    
13. FROM Orders
    
14. WHERE invoice > 20
    
15. GROUP BY DATE_FORMAT(order_date, '%Y-%m');

复制代码

1532. The Most Recent Three Orders
Medium
Topics
SQL Schema
Pandas Schema
Table: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
This table contains information about customers.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| cost          | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information about the orders made by customer_id.
Each customer has one order per day.


Write a solution to find the most recent three orders of each user. If a user ordered less than three orders, return all of their orders.

Return the result table ordered by customer_name in ascending order and in case of a tie by the customer_id in ascending order. If there is still a tie, order them by order_date in descending order.

The result format is in the following example.



Example 1:

Input:
Customers table:
+-------------+-----------+
| customer_id | name      |
+-------------+-----------+
| 1           | Winston   |
| 2           | Jonathan  |
| 3           | Annabelle |
| 4           | Marwan    |
| 5           | Khaled    |
+-------------+-----------+
Orders table:
+----------+------------+-------------+------+
| order_id | order_date | customer_id | cost |
+----------+------------+-------------+------+
| 1        | 2020-07-31 | 1           | 30   |
| 2        | 2020-07-30 | 2           | 40   |
| 3        | 2020-07-31 | 3           | 70   |
| 4        | 2020-07-29 | 4           | 100  |
| 5        | 2020-06-10 | 1           | 1010 |
| 6        | 2020-08-01 | 2           | 102  |
| 7        | 2020-08-01 | 3           | 111  |
| 8        | 2020-08-03 | 1           | 99   |
| 9        | 2020-08-07 | 2           | 32   |
| 10       | 2020-07-15 | 1           | 2    |
+----------+------------+-------------+------+
Output:
+---------------+-------------+----------+------------+
| customer_name | customer_id | order_id | order_date |
+---------------+-------------+----------+------------+
| Annabelle     | 3           | 7        | 2020-08-01 |
| Annabelle     | 3           | 3        | 2020-07-31 |
| Jonathan      | 2           | 9        | 2020-08-07 |
| Jonathan      | 2           | 6        | 2020-08-01 |
| Jonathan      | 2           | 2        | 2020-07-30 |
| Marwan        | 4           | 4        | 2020-07-29 |
| Winston       | 1           | 8        | 2020-08-03 |
| Winston       | 1           | 1        | 2020-07-31 |
| Winston       | 1           | 10       | 2020-07-15 |
+---------------+-------------+----------+------------+
Explanation:
Winston has 4 orders, we discard the order of "2020-06-10" because it is the oldest order.
Annabelle has only 2 orders, we return them.
Jonathan has exactly 3 orders.
Marwan ordered only one time.
We sort the result table by customer_name in ascending order, by customer_id in ascending order, and by order_date in descending order in case of a tie.


思路

这是经典的**分组取前 N 条（top-N per group）**问题：每位顾客只保留最近的 3 笔订单，不足 3 笔就全部保留。

对每位顾客，按 order_date 降序编号，最近的一笔是第 1，次近的是第 2，以此类推。
只保留编号 ≤ 3 的订单。
连接 Customers 取顾客姓名，最后按题目要求排序。

题目保证每位顾客每天最多一笔订单，所以同一顾客的 order_date 不会重复，排序没有歧义，用 ROW_NUMBER 就可以。

SQL 解法一：窗口函数（MySQL 8+）

1. # Write your MySQL query statement below
   
2. SELECT customer_name, customer_id, order_id, order_date
   
3. FROM (
   
4.     SELECT
   
5.         c.name AS customer_name,
   
6.         c.customer_id,
   
7.         o.order_id,
   
8.         o.order_date,
   
9.         ROW_NUMBER() OVER (
   
10.             PARTITION BY c.customer_id
    
11.             ORDER BY o.order_date DESC
    
12.         ) AS rn
    
13.     FROM Customers c
    
14.     JOIN Orders o
    
15.         ON c.customer_id = o.customer_id
    
16. ) t
    
17. WHERE rn <= 3
    
18. ORDER BY customer_name ASC, customer_id ASC, order_date DESC;v

复制代码