SQL | LeetCode 1581. 방문은 했지만 거래는 없던 고객 찾기 (RIGHT JOIN & GROUP BY 예제)

이 포스팅에서는 RIGHT JOIN을 활용해 거래가 일어나지 않은 고객 방문을 찾아보겠습니다. 일반적으로 LEFT JOIN이 더 익숙하겠지만, RIGHT JOIN으로도 같은 문제를 해결할 수 있습니다.

Table: Visits
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| visit_id    | int     |
| customer_id | int     |
+-------------+---------+
visit_id is the column with unique values 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 column with unique values for this table.
This table contains information about the transactions made during the visit_id.

 

Write a solution 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 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.

 

• Visits 테이블에는 방문 정보가 담겨 있습니다.
• Transactions 테이블에는 거래 정보가 담겨 있습니다.
• 거래가 없는 방문(Transactions에 기록이 없는 visit_id)을 찾아, 고객별(customer_id)로 몇 번 있었는지 계산하세요.

mysql> SELECT * FROM Visits;
+----------+-------------+
| visit_id | customer_id |
+----------+-------------+
|        1 |          23 |
|        2 |           9 |
|        4 |          30 |
|        5 |          54 |
|        6 |          96 |
|        7 |          54 |
|        8 |          54 |
+----------+-------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM Transactions;
+----------------+----------+--------+
| transaction_id | visit_id | amount |
+----------------+----------+--------+
|              2 |        5 |   NULL |
|              3 |        5 |    300 |
|              9 |        5 |    200 |
|             12 |        1 |    910 |
|             13 |        2 |    970 |
+----------------+----------+--------+
5 rows in set (0.00 sec)

 

임의의 데이터를 만들어 줘 봤습니다.

mysql> SELECT * FROM Transactions t RIGHT JOIN Visits v ON t.visit_id = v.visit_id;
+----------------+----------+--------+----------+-------------+
| transaction_id | visit_id | amount | visit_id | customer_id |
+----------------+----------+--------+----------+-------------+
|              2 |        5 |   NULL |        5 |          54 |
|              3 |        5 |    300 |        5 |          54 |
|              9 |        5 |    200 |        5 |          54 |
|             12 |        1 |    910 |        1 |          23 |
|             13 |        2 |    970 |        2 |           9 |
|           NULL |     NULL |   NULL |        4 |          30 |
|           NULL |     NULL |   NULL |        6 |          96 |
|           NULL |     NULL |   NULL |        7 |          54 |
|           NULL |     NULL |   NULL |        8 |          54 |
+----------------+----------+--------+----------+-------------+
9 rows in set (0.00 sec)

RIGHT JOIN을 하면 오른쪽 테이블의 값은 다 프린트 되고 매칭되지 않는 왼쪽 테이블의 값은 NULL로 표기됩니다. 

mysql> SELECT * FROM Transactions t RIGHT JOIN Visits v ON t.visit_id = v.visit_id WHERE t.transaction_id IS NULL;
+----------------+----------+--------+----------+-------------+
| transaction_id | visit_id | amount | visit_id | customer_id |
+----------------+----------+--------+----------+-------------+
|           NULL |     NULL |   NULL |        4 |          30 |
|           NULL |     NULL |   NULL |        6 |          96 |
|           NULL |     NULL |   NULL |        7 |          54 |
|           NULL |     NULL |   NULL |        8 |          54 |
+----------------+----------+--------+----------+-------------+
4 rows in set (0.01 sec)

우리는 거래가 일어나지 않은 고객 방문에 관심이 있으므로, T.transaction_id는 NULL인 경우가 필요합니다.

mysql> SELECT v.customer_id, COUNT(*) FROM Transactions t RIGHT JOIN Visits v ON t.visit_id = v.visit_id WHERE t.transaction_id IS NULL GROUP BY v.customer_id;
+-------------+----------+
| customer_id | COUNT(*) |
+-------------+----------+
|          30 |        1 |
|          54 |        2 |
|          96 |        1 |
+-------------+----------+
3 rows in set (0.01 sec)

거래가 없는 방문을 customer_id별로 그룹화하여 COUNT(*)로 횟수를 셉니다.