➕ Combine Two Tables
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| personId | int |
| lastName | varchar |
| firstName | varchar |
+-------------+---------+
personId is the primary key column for this table.
This table contains information about the ID of some persons and their first and last names.
Table: Address
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| addressId | int |
| personId | int |
| city | varchar |
| state | varchar |
+-------------+---------+
addressId is the primary key column for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1 | Wang | Allen |
| 2 | Alice | Bob |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city | state |
+-----------+----------+---------------+------------+
| 1 | 2 | New York City | New York |
| 2 | 3 | Leetcode | California |
+-----------+----------+---------------+------------+
Output:
+-----------+----------+---------------+----------+
| firstName | lastName | city | state |
+-----------+----------+---------------+----------+
| Allen | Wang | Null | Null |
| Bob | Alice | New York City | New York |
+-----------+----------+---------------+----------+
Explanation:
There is no address in the address table for the personId = 1 so we return null in their city and state.
addressId = 1 contains information about the address of personId = 2.✅ 해답
여기서는
위의 Person, Address 테이블에서
각각 firstName, lastName, city, state 값을 뽑아내어 재구성 하는 것이다.
이를 적용하기 위해서는 간단하게 LEFT JOIN을 활용하면 된다.
SELECT a.firstName, a.lastName, b.city, b.state FROM Person a
LEFT JOIN Address b
ON a.personId = b.personId;🤷🏻 Customer Who Visited but Did Not Make Any Transactions
Table: Visits
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| visit_id | int |
| customer_id | int |
+-------------+---------+
visit_id is the primary key for this table.
This table contains information about the customers who visited the mall.
Table: Transactions
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| transaction_id | int |
| visit_id | int |
| amount | int |
+----------------+---------+
transaction_id is the primary key for this table.
This table contains information about the transactions made during the visit_id.
Write an SQL query to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits.
Return the result table sorted in any order.
The query result format is in the following example.
Example 1:
Input:
Visits
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
| 1 | 23 |
| 2 | 9 |
| 4 | 30 |
| 5 | 54 |
| 6 | 96 |
| 7 | 54 |
| 8 | 54 |
+----------+-------------+
Transactions
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
| 2 | 5 | 310 |
| 3 | 5 | 300 |
| 9 | 5 | 200 |
| 12 | 1 | 910 |
| 13 | 2 | 970 |
+----------------+----------+--------+
Output:
+-------------+----------------+
| customer_id | count_no_trans |
+-------------+----------------+
| 54 | 2 |
| 30 | 1 |
| 96 | 1 |
+-------------+----------------+
Explanation:
Customer with id = 23 visited the mall once and made one transaction during the visit with id = 12.
Customer with id = 9 visited the mall once and made one transaction during the visit with id = 13.
Customer with id = 30 visited the mall once and did not make any transactions.
Customer with id = 54 visited the mall three times. During 2 visits they did not make any transactions, and during one visit they made 3 transactions.
Customer with id = 96 visited the mall once and did not make any transactions.
As we can see, users with IDs 30 and 96 visited the mall one time without making any transactions. Also, user 54 visited the mall twice and did not make any transactions.✅ 해답
여기서는
방문은 했지만 아무 활동도 하지 않는 customer_id를 찾고, 그들이 몇번 방문했는지를 반환
하는 쿼리를 짜면 된다.
Example 1 테이블에서 장황하게 설명을 하지만 결국 아래와 같이 쿼리를 짜면 된다.
일단, Visits, Transactions 테이블을 JOIN시켜주어야
visited_id를 통하여 어느 customer_id가 활동이 있었는지 없었는지를 알 수 있다.
그리고 Visits 테이블에 LEFT JOIN을 실행하면
Transactions 테이블에서 Visits 테이블에 값이 없는 visited_id의 나머지 column 값들이 NULL로 표기되기 때문에
b.visited_id의 값에서 NULL인 값만 찾아주면
아무 활동도 하지 않은 customer_id가 나오고
이를 그 customer_id로 묶어 Visits 테이블의 아무 열의 개수만 세어주면 그 횟수가 나온다.
SELECT a.customer_id, COUNT(a.visit_id) AS count_no_trans FROM Visits a
LEFT JOIN Transactions b
ON a.visit_id = b.visit_id
WHERE b.visit_id IS NULL
GROUP BY customer_id;
>>>
customer_id count_no_trans
'30','1'
'96','1'
'54','2'
Discussion 해답(with구문 사용)
with cte as (
select visit_id, customer_id
from Visits
where visit_id not in (select visit_id from Transactions))
select customer_id, count(*) as count_no_trans
from cte
group by customer_id;여기서는 먼저 WITH 구문을 통해 Transaction 테이블에서 visit_id가 없는 것을 조건에 걸러진 visit_id, customer_id 값을 Visits 테이블에서 반환한
cte라는 테이블을 만든 뒤
그 안에서 customer_id와 그 개수를 세고 그룹핑을 해주었다.
심플하게는 조인을 사용하지 않은 방법이다.
👀 Article Views I
Table: Views
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| article_id | int |
| author_id | int |
| viewer_id | int |
| view_date | date |
+---------------+---------+
There is no primary key for this table, it 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 an SQL query to find all the authors that viewed at least one of their own articles.
Return the result table sorted by id in ascending order.
The query 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 |
| 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 |
+------+
| 4 |
| 7 |
+------+✅ 해답
여기서는
Views 테이블의 author_id 중 viewer_id와 동일한 값을 가진 author_id를 찾아 id로 이름 붙히고 오름차순으로 정렬한 값을 반환하면 된다.
SELECT author_id AS id FROM Views
WHERE author_id = viewer_id
GROUP BY id
ORDER BY id;만약 GROUP BY id구문을 사용하고 싶지 않다면 대신
SELECT DISTINCT(author_id) 로 작성하는 것도 한 방법이다.
다음에는 Day 6 문제들에 대하여 알아보도록 하자.
