[리트코드] Medium|1158. Market Analysis I; LEFT JOIN
1. Problem
모든 사용자에 대해 가입일과 2019년에 구매한 주문 건수를 구해야 한다.
- 핵심 과제: 2019년에 주문을 한 번도 하지 않은 사용자(3번, 4번 유저)도 결과에 반드시 포함되어야 하며, 이들의 주문 건수는 0으로 표시되어야 한다.
2. Solution: 기준 테이블 보존과 ON 절 필터링
모든 사용자를 유지하기 위해 Users를 왼쪽(Left)에 두고, 2019년 조건은 조인 단계(ON)에서 처리한다.
3. Takeaway: 왜 WHERE 절을 쓰면 오답이 되는가? (객관적 분석)
- WHERE 절의 강력한 필터링 (유진 님의 오답 분석):
- LEFT JOIN 후에 WHERE YEAR(order_date) = 2019를 사용하면, 주문 기록이 없어 order_date가 NULL인 행들이 모두 제거된다. 결과적으로 INNER JOIN을 한 것과 같은 데이터 유실이 발생한다.
- ON 절의 유연함:
- ON 절에 조건을 넣으면, 오른쪽 테이블(Orders)에서 조건에 맞는 데이터만 골라와서 왼쪽 테이블(Users)에 붙인다. 조건에 맞는 게 없더라도 왼쪽 테이블의 행은 그대로 살아남고 오른쪽만 NULL로 채워진다.
- 기준 테이블 선정의 중요성:
- Orders를 왼쪽에 두면 주문을 한 번도 안 한 유저는 Orders 테이블에 아예 존재하지 않으므로 처음부터 집계 대상에서 제외되는 치명적인 오류가 생긴다.
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. 정답
SELECT u.user_id AS buyer_id,
u.join_date,
count(o.order_id) AS orders_in_2019
FROM Users u
LEFT JOIN Orders o ON o.buyer_id = u.user_id
AND YEAR(o.order_date) = 2019 # Orders 테이블에서 2019년 주문만 가져온다 (필터링)
GROUP BY u.user_id, u.join_date; # SELECT 절에 집계함수를 제외한 컬럼이 있다면, 반드시 GROUP BY에 명시할 것
2. 나의 오답
# 오답!!!
SELECT o.buyer_id, u.join_date, count(*) AS orders_in_2019
FROM Orders o
LEFT JOIN Users u ON o.buyer_id = u.user_id # Orders 테이블을 왼쪽에 두면 Users 테이블의 주문안한 고객이 지워져버림
WHERE order_date LIKE '2019%' # order_date 가 NULL인, 주문안한 고객들이 지워짐. 주문을 안했더라도 모든 user를 출력해야함.
GROUP BY o.buyer_id;