# LeetCode Solution, Easy, 1158. Market Analysis I

# [1158. Market Analysis I](https://leetcode.com/problems/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

```sql
# 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
```