1581. Customer Who Visited but Did Not Make Any Transactions

문제

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.

해설

SELECT v.customer_id, COUNT(v.visit_id) AS visits_without_transactions
FROM Visits v
LEFT JOIN Transactions t
    ON v.visit_id = t.visit_id
WHERE t.transaction_id IS NULL
GROUP BY v.customer_id;

FROM Visits v

• Visits 테이블에서 시작

LEFT JOIN Transactions t ON v.visit_id=t.visit_id

• Transactions 테이블을 LEFT JOIN으로 결합
• LEFT JOIN은 왼쪽 테이블(Visits)의 모든 레코드를 포함하고, 오른쪽 테이블(Transactions)에 해당하는 값이 없으면 NULL로 채워진다.
• 이를 통해서 거래가 없는 방문 값을 포함

WHERE t.transaction_id IS NULL

• Transactions 테이블에 해당하는 거래가 없다면 transaction_id는 NULL
• 따라서 transaction_id가 NULL인 경우만 필터링

GROUP BY v.customer_id

• 고객별로 그룹화
• 같은 customer_id를 가진 모든 방문을 하나로 묶음

COUNT(v.visit_id) AS count_no_trans

• 각 그룹에서 거래가 없는 방문의 수 카운트