1158. Market Analysis I

題目敘述

SQL Schema >

Create table If Not Exists Users (user_id int, join_date date, favorite_brand varchar(10)) Create table If Not Exists Orders (order_id int, order_date date, item_id int, buyer_id int, seller_id int) Create table If Not Exists Items (item_id int, item_brand varchar(10)) Truncate table Users insert into Users (user_id, join_date, favorite_brand) values ('1', '2018-01-01', 'Lenovo') insert into Users (user_id, join_date, favorite_brand) values ('2', '2018-02-09', 'Samsung') insert into Users (user_id, join_date, favorite_brand) values ('3', '2018-01-19', 'LG') insert into Users (user_id, join_date, favorite_brand) values ('4', '2018-05-21', 'HP') Truncate table Orders insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('1', '2019-08-01', '4', '1', '2') insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('2', '2018-08-02', '2', '1', '3') insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('3', '2019-08-03', '3', '2', '3') insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('4', '2018-08-04', '1', '4', '2') insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('5', '2018-08-04', '1', '3', '4') insert into Orders (order_id, order_date, item_id, buyer_id, seller_id) values ('6', '2019-08-05', '2', '2', '4') Truncate table Items insert into Items (item_id, item_brand) values ('1', 'Samsung') insert into Items (item_id, item_brand) values ('2', 'Lenovo') insert into Items (item_id, item_brand) values ('3', 'LG') insert into Items (item_id, item_brand) values ('4', 'HP')

Table: Users

+----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ user_id is the primary key 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 of this table. item_id is a foreign key 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 of this table.

Write an SQL query 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 query 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 | +-----------+------------+----------------+

題目翻譯

這邊有三張表 Users, Orders, Items，找出每個使用者加入的時間和在 2019 年購買了多少訂單，不要求排序。

解法解析

這邊有提到幾點說明為什麼不使用 WHERE 為什麼不使用 ISNULL，因為按照 SQL 執行的順序會是：

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

如果我們在 WHERE 子句中使用 Year is 2019，它將在執行 GROUP BY 或 SELECT(COUNT)之前，先移除那些沒有在 2019 年下單的用戶，這意味著我們會錯過那些在 2019 年沒有下單但仍需要在最終結果集中包含的用戶，並為他們的 orders_in_2019 設置為 0。相反，我們使用 LEFT JOIN 來將 Users 表格與 Orders 表格連接起來，透過這個方式篩選出只有當 buyer_id 和 user_id 相符且 order_date 的年份為 2019 的行，這樣就能確保所有的用戶都包含在最終結果集中，即使他們在 2019 年沒有下單。

解法範例

MySQL

# Write your MySQL query statement below
SELECT u.user_id AS buyer_id,
    join_date,
    COUNT(order_date) AS orders_in_2019
FROM Users as u
    LEFT JOIN Orders as o ON u.user_id = o.buyer_id
    AND YEAR(order_date) = '2019'
GROUP BY u.user_id