刷题记录帖子

1098. Unpopular Books
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Books

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id is the primary key (column with unique values) of this table.


Table: Orders

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id is the primary key (column with unique values) of this table.
book_id is a foreign key (reference column) to the Books table.


Write a solution to report the books that have sold less than 10 copies in the last year, excluding books that have been available for less than one month from today. Assume today is 2019-06-23.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Books table:
+---------+--------------------+----------------+
| book_id | name               | available_from |
+---------+--------------------+----------------+
| 1       | "Kalila And Demna" | 2010-01-01     |
| 2       | "28 Letters"       | 2012-05-12     |
| 3       | "The Hobbit"       | 2019-06-10     |
| 4       | "13 Reasons Why"   | 2019-06-01     |
| 5       | "The Hunger Games" | 2008-09-21     |
+---------+--------------------+----------------+
Orders table:
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1        | 1       | 2        | 2018-07-26    |
| 2        | 1       | 1        | 2018-11-05    |
| 3        | 3       | 8        | 2019-06-11    |
| 4        | 4       | 6        | 2019-06-05    |
| 5        | 4       | 5        | 2019-06-20    |
| 6        | 5       | 9        | 2009-02-02    |
| 7        | 5       | 8        | 2010-04-13    |
+----------+---------+----------+---------------+
Output:
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

1. 
   
2. SELECT
   
3.     b.book_id,
   
4.     b.name
   
5. FROM Books b
   
6. LEFT JOIN Orders o
   
7.     ON b.book_id = o.book_id
   
8.     AND o.dispatch_date BETWEEN '2018-06-23' AND '2019-06-23'
   
9. WHERE b.available_from <= '2019-05-23'
   
10. GROUP BY b.book_id, b.name
    
11. HAVING COALESCE(SUM(o.quantity), 0) < 10;
    

复制代码

1113. Reported Posts
Easy
Topics
SQL Schema
Pandas Schema
Table: Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    |
| action        | enum    |
| extra         | varchar |
+---------------+---------+
This table may have duplicate rows.
The action column is an ENUM (category) type of ('view', 'like', 'reaction', 'comment', 'report', 'share').
The extra column has optional information about the action, such as a reason for the report or a type of reaction.


Write a solution to report the number of posts reported yesterday for each report reason. Assume today is 2019-07-05.

Return the result table in any order.

The result format is in the following example.

1. 
   
2. # Write your MySQL query statement below
   
3. 
   
4. SELECT
   
5.     extra AS report_reason,
   
6.     COUNT(DISTINCT post_id) AS report_count
   
7. FROM Actions
   
8. WHERE action = 'report'
   
9.   AND action_date = '2019-07-04'
   
10. GROUP BY extra;
    

复制代码

1126. Active Businesses
Medium
Topics
SQL Schema
Pandas Schema
Table: Events

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| business_id   | int     |
| event_type    | varchar |
| occurrences   | int     |
+---------------+---------+
(business_id, event_type) is the primary key (combination of columns with unique values) of this table.
Each row in the table logs the info that an event of some type occurred at some business for a number of times.
The average activity for a particular event_type is the average occurrences across all companies that have this event.

An active business is a business that has more than one event_type such that their occurrences is strictly greater than the average activity for that event.

Write a solution to find all active businesses.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Events table:
+-------------+------------+-------------+
| business_id | event_type | occurrences |
+-------------+------------+-------------+
| 1           | reviews    | 7           |
| 3           | reviews    | 3           |
| 1           | ads        | 11          |
| 2           | ads        | 7           |
| 3           | ads        | 6           |
| 1           | page views | 3           |
| 2           | page views | 12          |
+-------------+------------+-------------+
Output:
+-------------+
| business_id |
+-------------+
| 1           |
+-------------+
Explanation:  
The average activity for each event can be calculated as follows:
- 'reviews': (7+3)/2 = 5
- 'ads': (11+7+6)/3 = 8
- 'page views': (3+12)/2 = 7.5
The business with id=1 has 7 'reviews' events (more than 5) and 11 'ads' events (more than 8), so it is an active business.

1. 
   
2. WITH event_avg AS (
   
3.     SELECT
   
4.         event_type,
   
5.         AVG(occurrences) AS avg_occurrences
   
6.     FROM Events
   
7.     GROUP BY event_type
   
8. )
   
9. SELECT
   
10.     e.business_id
    
11. FROM Events e
    
12. JOIN event_avg a
    
13.     ON e.event_type = a.event_type
    
14. WHERE e.occurrences > a.avg_occurrences
    
15. GROUP BY e.business_id
    
16. HAVING COUNT(*) > 1;
    

复制代码

1127. User Purchase Platform
Hard
Topics
SQL Schema
Pandas Schema
Table: Spending

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| spend_date  | date    |
| platform    | enum    |
| amount      | int     |
+-------------+---------+
The table logs the history of the spending of users that make purchases from an online shopping website that has a desktop and a mobile application.
(user_id, spend_date, platform) is the primary key (combination of columns with unique values) of this table.
The platform column is an ENUM (category) type of ('desktop', 'mobile').


Write a solution to find the total number of users and the total amount spent using the mobile only, the desktop only, and both mobile and desktop together for each date.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1       | 2019-07-01 | mobile   | 100    |
| 1       | 2019-07-01 | desktop  | 100    |
| 2       | 2019-07-01 | mobile   | 100    |
| 2       | 2019-07-02 | mobile   | 100    |
| 3       | 2019-07-01 | desktop  | 100    |
| 3       | 2019-07-02 | desktop  | 100    |
+---------+------------+----------+--------+
Output:
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop  | 100          | 1           |
| 2019-07-01 | mobile   | 100          | 1           |
| 2019-07-01 | both     | 200          | 1           |
| 2019-07-02 | desktop  | 100          | 1           |
| 2019-07-02 | mobile   | 100          | 1           |
| 2019-07-02 | both     | 0            | 0           |
+------------+----------+--------------+-------------+
Explanation:
On 2019-07-01, user 1 purchased using both desktop and mobile, user 2 purchased using mobile only and user 3 purchased using desktop only.
On 2019-07-02, user 2 purchased using mobile only, user 3 purchased using desktop only and no one purchased using both platforms.

1. # Write your MySQL query statement below
   
2. 
   
3. WITH user_daily AS (
   
4.     SELECT
   
5.         user_id,
   
6.         spend_date,
   
7.         SUM(amount) AS total_amount,
   
8.         COUNT(DISTINCT platform) AS platform_count,
   
9.         MAX(platform) AS platform
   
10.     FROM Spending
    
11.     GROUP BY user_id, spend_date
    
12. ),
    
13. daily_summary AS (
    
14.     SELECT
    
15.         spend_date,
    
16.         CASE
    
17.             WHEN platform_count = 2 THEN 'both'
    
18.             ELSE platform
    
19.         END AS platform,
    
20.         SUM(total_amount) AS total_amount,
    
21.         COUNT(*) AS total_users
    
22.     FROM user_daily
    
23.     GROUP BY
    
24.         spend_date,
    
25.         CASE
    
26.             WHEN platform_count = 2 THEN 'both'
    
27.             ELSE platform
    
28.         END
    
29. ),
    
30. dates AS (
    
31.     SELECT DISTINCT spend_date
    
32.     FROM Spending
    
33. ),
    
34. platforms AS (
    
35.     SELECT 'desktop' AS platform
    
36.     UNION ALL
    
37.     SELECT 'mobile'
    
38.     UNION ALL
    
39.     SELECT 'both'
    
40. )
    
41. SELECT
    
42.     d.spend_date,
    
43.     p.platform,
    
44.     COALESCE(s.total_amount, 0) AS total_amount,
    
45.     COALESCE(s.total_users, 0) AS total_users
    
46. FROM dates d
    
47. CROSS JOIN platforms p
    
48. LEFT JOIN daily_summary s
    
49.     ON d.spend_date = s.spend_date
    
50.    AND p.platform = s.platform;
    

复制代码

1132. Reported Posts II
Medium
Topics
SQL Schema
Pandas Schema
Table: Actions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| post_id       | int     |
| action_date   | date    |
| action        | enum    |
| extra         | varchar |
+---------------+---------+
This table may have duplicate rows.
The action column is an ENUM (category) type of ('view', 'like', 'reaction', 'comment', 'report', 'share').
The extra column has optional information about the action, such as a reason for the report or a type of reaction.


Table: Removals

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| post_id       | int     |
| remove_date   | date    |
+---------------+---------+
post_id is the primary key (column with unique values) of this table.
Each row in this table indicates that some post was removed due to being reported or as a result of an admin review.


Write a solution to find the average daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places.

The result format is in the following example.



Example 1:

Input:
Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra  |
+---------+---------+-------------+--------+--------+
| 1       | 1       | 2019-07-01  | view   | null   |
| 1       | 1       | 2019-07-01  | like   | null   |
| 1       | 1       | 2019-07-01  | share  | null   |
| 2       | 2       | 2019-07-04  | view   | null   |
| 2       | 2       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | view   | null   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 4       | 3       | 2019-07-02  | view   | null   |
| 4       | 3       | 2019-07-02  | report | spam   |
| 5       | 2       | 2019-07-03  | view   | null   |
| 5       | 2       | 2019-07-03  | report | racism |
| 5       | 5       | 2019-07-03  | view   | null   |
| 5       | 5       | 2019-07-03  | report | racism |
+---------+---------+-------------+--------+--------+
Removals table:
+---------+-------------+
| post_id | remove_date |
+---------+-------------+
| 2       | 2019-07-20  |
| 3       | 2019-07-18  |
+---------+-------------+
Output:
+-----------------------+
| average_daily_percent |
+-----------------------+
| 75.00                 |
+-----------------------+
Explanation:
The percentage for 2019-07-04 is 50% because only one post of two spam reported posts were removed.
The percentage for 2019-07-02 is 100% because one post was reported as spam and it was removed.
The other days had no spam reports so the average is (50 + 100) / 2 = 75%
Note that the output is only one number and that we do not care about the remove dates.

1. 
   
2. WITH daily_reports AS (
   
3.     SELECT
   
4.         a.action_date,
   
5.         COUNT(DISTINCT a.post_id) AS reported_posts,
   
6.         COUNT(DISTINCT r.post_id) AS removed_posts
   
7.     FROM Actions a
   
8.     LEFT JOIN Removals r
   
9.         ON a.post_id = r.post_id
   
10.     WHERE a.action = 'report'
    
11.       AND a.extra = 'spam'
    
12.     GROUP BY a.action_date
    
13. )
    
14. SELECT
    
15.     ROUND(
    
16.         AVG(removed_posts * 100.0 / reported_posts),
    
17.         2
    
18.     ) AS average_daily_percent
    
19. FROM daily_reports;
    

复制代码

先按举报日期计算被举报为 spam 的不同帖子中，有多少比例最终被删除，再对这些有 spam 举报的日期的百分比求平均。

1141. User Activity for the Past 30 Days I
Easy
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
This table may have duplicate rows.
The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.


Write a solution to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.

Return the result table in any order.

The result format is in the following example.

Note: Any activity from ('open_session', 'end_session', 'scroll_down', 'send_message') will be considered valid activity for a user to be considered active on a day.



Example 1:

Input:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
Output:
+------------+--------------+
| day        | active_users |
+------------+--------------+
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+
Explanation: Note that we do not care about days with zero active users.

1. # Write your MySQL query statement below
   
2. 
   
3. SELECT
   
4.     activity_date AS day,
   
5.     COUNT(DISTINCT user_id) AS active_users
   
6. FROM Activity
   
7. WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY)
   
8.                         AND '2019-07-27'
   
9. GROUP BY activity_date;
   

复制代码

1142. User Activity for the Past 30 Days II
Easy
Topics
SQL Schema
Pandas Schema
Table: Activity

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
This table may have duplicate rows.
The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.


Write a solution to find the average number of sessions per user for a period of 30 days ending 2019-07-27 inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period.

The result format is in the following example.



Example 1:

Input:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 3       | 5          | 2019-07-21    | open_session  |
| 3       | 5          | 2019-07-21    | scroll_down   |
| 3       | 5          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
Output:
+---------------------------+
| average_sessions_per_user |
+---------------------------+
| 1.33                      |
+---------------------------+
Explanation: User 1 and 2 each had 1 session in the past 30 days while user 3 had 2 sessions so the average is (1 + 1 + 2) / 3 = 1.33.

1. # Write your MySQL query statement below
   
2. 
   
3. WITH user_sessions AS (
   
4.     SELECT
   
5.         user_id,
   
6.         COUNT(DISTINCT session_id) AS session_count
   
7.     FROM Activity
   
8.     WHERE activity_date BETWEEN DATE_SUB('2019-07-27', INTERVAL 29 DAY)
   
9.                             AND '2019-07-27'
   
10.     GROUP BY user_id
    
11. )
    
12. SELECT
    
13.     COALESCE(ROUND(AVG(session_count), 2), 0) AS average_sessions_per_user
    
14. FROM user_sessions;
    

复制代码

1149. Article Views II
Medium
Topics
SQL Schema
Pandas Schema
Table: Views

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| article_id    | int     |
| author_id     | int     |
| viewer_id     | int     |
| view_date     | date    |
+---------------+---------+
This table may have duplicate rows.
Each row of this table indicates that some viewer viewed an article (written by some author) on some date.
Note that equal author_id and viewer_id indicate the same person.


Write a solution to find all the people who viewed more than one article on the same date.

Return the result table sorted by id in ascending order.

The result format is in the following example.



Example 1:

Input:
Views table:
+------------+-----------+-----------+------------+
| article_id | author_id | viewer_id | view_date  |
+------------+-----------+-----------+------------+
| 1          | 3         | 5         | 2019-08-01 |
| 3          | 4         | 5         | 2019-08-01 |
| 1          | 3         | 6         | 2019-08-02 |
| 2          | 7         | 7         | 2019-08-01 |
| 2          | 7         | 6         | 2019-08-02 |
| 4          | 7         | 1         | 2019-07-22 |
| 3          | 4         | 4         | 2019-07-21 |
| 3          | 4         | 4         | 2019-07-21 |
+------------+-----------+-----------+------------+
Output:
+------+
| id   |
+------+
| 5    |
| 6    |
+------+

1. SELECT DISTINCT viewer_id AS id
   
2. FROM Views
   
3. GROUP BY viewer_id, view_date
   
4. HAVING COUNT(DISTINCT article_id) > 1
   
5. ORDER BY id;

复制代码

1158. Market Analysis I
Medium
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id is the primary key (column with unique values) of this table.
This table has the info of the users of an online shopping website where users can sell and buy items.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id is the primary key (column with unique values) of this table.
item_id is a foreign key (reference column) to the Items table.
buyer_id and seller_id are foreign keys to the Users table.


Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the primary key (column with unique values) of this table.


Write a solution to find for each user, the join date and the number of orders they made as a buyer in 2019.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2018-01-01 | Lenovo         |
| 2       | 2018-02-09 | Samsung        |
| 3       | 2018-01-19 | LG             |
| 4       | 2018-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2018-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2018-08-04 | 1       | 4        | 2         |
| 5        | 2018-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output:
+-----------+------------+----------------+
| buyer_id  | join_date  | orders_in_2019 |
+-----------+------------+----------------+
| 1         | 2018-01-01 | 1              |
| 2         | 2018-02-09 | 2              |
| 3         | 2018-01-19 | 0              |
| 4         | 2018-05-21 | 0              |
+-----------+------------+----------------+

1. # Write your MySQL query statement below
   
2. SELECT
   
3.     u.user_id AS buyer_id,
   
4.     u.join_date,
   
5.     COUNT(o.order_id) AS orders_in_2019
   
6. FROM Users u
   
7. LEFT JOIN Orders o
   
8.     ON u.user_id = o.buyer_id
   
9.     AND o.order_date >= '2019-01-01'
   
10.     AND o.order_date < '2020-01-01'
    
11. GROUP BY
    
12.     u.user_id,
    
13.     u.join_date;

复制代码

为什么这样写？
1. 使用 LEFT JOIN：保留所有用户，即使用户没有在 2019 年下过订单，也会显示在结果中。
2. 日期条件放在 ON 中：这是关键。如果把日期条件放在 WHERE 中，没有符合条件的订单的用户就会被过滤掉，无法显示订单数为 0 的用户。
3. 使用 COUNT(o.order_id)：对于没有匹配订单的用户，o.order_id 是 NULL，COUNT 不会统计 NULL，因此结果为 0。
4. 使用半开区间筛选日期：>= '2019-01-01' AND < '2020-01-01' 能覆盖整个 2019 年，即使日期字段以后改成包含时间的 DATETIME，也不会遗漏 2019 年最后一天的记录。

1159. Market Analysis II
Hard
Topics
conpanies icon
Companies
SQL Schema
Pandas Schema
Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| join_date      | date    |
| favorite_brand | varchar |
+----------------+---------+
user_id is the primary key (column with unique values) of this table.
This table has the info of the users of an online shopping website where users can sell and buy items.


Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| item_id       | int     |
| buyer_id      | int     |
| seller_id     | int     |
+---------------+---------+
order_id is the primary key (column with unique values) of this table.
item_id is a foreign key (reference column) to the Items table.
buyer_id and seller_id are foreign keys to the Users table.


Table: Items

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| item_id       | int     |
| item_brand    | varchar |
+---------------+---------+
item_id is the primary key (column with unique values) of this table.


Write a solution to find for each user whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as no. It is guaranteed that no seller sells more than one item in a day.

Return the result table in any order.

The result format is in the following example.



Example 1:

Input:
Users table:
+---------+------------+----------------+
| user_id | join_date  | favorite_brand |
+---------+------------+----------------+
| 1       | 2019-01-01 | Lenovo         |
| 2       | 2019-02-09 | Samsung        |
| 3       | 2019-01-19 | LG             |
| 4       | 2019-05-21 | HP             |
+---------+------------+----------------+
Orders table:
+----------+------------+---------+----------+-----------+
| order_id | order_date | item_id | buyer_id | seller_id |
+----------+------------+---------+----------+-----------+
| 1        | 2019-08-01 | 4       | 1        | 2         |
| 2        | 2019-08-02 | 2       | 1        | 3         |
| 3        | 2019-08-03 | 3       | 2        | 3         |
| 4        | 2019-08-04 | 1       | 4        | 2         |
| 5        | 2019-08-04 | 1       | 3        | 4         |
| 6        | 2019-08-05 | 2       | 2        | 4         |
+----------+------------+---------+----------+-----------+
Items table:
+---------+------------+
| item_id | item_brand |
+---------+------------+
| 1       | Samsung    |
| 2       | Lenovo     |
| 3       | LG         |
| 4       | HP         |
+---------+------------+
Output:
+-----------+--------------------+
| seller_id | 2nd_item_fav_brand |
+-----------+--------------------+
| 1         | no                 |
| 2         | yes                |
| 3         | yes                |
| 4         | no                 |
+-----------+--------------------+
Explanation:
The answer for the user with id 1 is no because they sold nothing.
The answer for the users with id 2 and 3 is yes because the brands of their second sold items are their favorite brands.
The answer for the user with id 4 is no because the brand of their second sold item is not their favorite brand.

1. # Write your MySQL query statement below
   
2. WITH ranked_orders AS (
   
3.     SELECT
   
4.         seller_id,
   
5.         item_id,
   
6.         ROW_NUMBER() OVER (
   
7.             PARTITION BY seller_id
   
8.             ORDER BY order_date
   
9.         ) AS rn
   
10.     FROM Orders
    
11. )
    
12. SELECT
    
13.     u.user_id AS seller_id,
    
14.     CASE
    
15.         WHEN i.item_brand = u.favorite_brand THEN 'yes'
    
16.         ELSE 'no'
    
17.     END AS 2nd_item_fav_brand
    
18. FROM Users u
    
19. LEFT JOIN ranked_orders r
    
20.     ON u.user_id = r.seller_id
    
21.     AND r.rn = 2
    
22. LEFT JOIN Items i
    
23.     ON r.item_id = i.item_id;

复制代码

解题思路
1. 用 ROW_NUMBER() 给每个卖家的订单排序
ROW_NUMBER() OVER (
    PARTITION BY seller_id
    ORDER BY order_date
) AS rn


- PARTITION BY seller_id：每个卖家独立编号。
- ORDER BY order_date：按出售日期从早到晚排序。
- ROW_NUMBER()：依次编号为 1、2、3……
题目保证每个卖家每天最多卖出一件商品，因此不需要处理同一天的排序冲突。
2. 只保留第二笔订单
ON u.user_id = r.seller_id
AND r.rn = 2


将条件 r.rn = 2 放在 LEFT JOIN 的 ON 子句中，可以保留所有用户，包括没有卖过商品或只卖过一件商品的用户。
3. 判断品牌是否相同
CASE
    WHEN i.item_brand = u.favorite_brand THEN 'yes'
    ELSE 'no'
END


- 第二件商品的品牌与最喜欢的品牌相同：yes
- 没有第二笔订单，或者品牌不同：no
为什么必须从 Users 开始使用 LEFT JOIN？
因为题目要求输出每一个用户。
如果从 Orders 开始查询，或者使用 INNER JOIN，没有卖出至少两件商品的用户就会被排除，无法输出题目要求的 no。
面试需要掌握的知识点
知识点        作用
ROW_NUMBER()        找每组中的第 N 条记录
PARTITION BY        按卖家分别排序
LEFT JOIN        保留没有匹配订单的用户
CASE WHEN        将比较结果转换成 yes / no
记忆技巧： 遇到“每个用户的第 N 笔订单”“第二次购买”“最近一次交易”等问题，优先考虑 ROW_NUMBER()。如果题目要求保留没有对应记录的人，再考虑从主表出发使用 LEFT JOIN。