SQL문제풀기

[SQL 문제 풀이] Customer Who Visited but Did Not Make Any Transactions (방문했지만 거래를 하지 않은 고객)

Jun 02, 2025

[SQL 문제 풀이] Customer Who Visited but Did Not Make Any Transactions (방문했지만 거래를 하지 않은 고객)

Contents

내가 작성한 쿼리

Customer Who Visited but Did Not Make Any Transactions - LeetCode

Can you solve this real interview question? 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. 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.

https://leetcode.com/problems/customer-who-visited-but-did-not-make-any-transactions/description/

Customer Who Visited but Did Not Make Any Transactions - LeetCode

notion image


거래를 하지 않고 방문한 사용자의 ID와 이러한 유형의 방문 횟수를 찾기 위한 솔루션을 작성하세요.

결과 테이블을 순서대로 정렬하여 반환합니다.

내가 작성한 쿼리

MySQL, Oracle


SELECT
    V.customer_id,
    COUNT(*) AS count_no_trans
FROM Visits V
LEFT JOIN Transactions T
    ON V.visit_id = T.visit_id
WHERE T.transaction_id IS NULL
GROUP BY customer_id;

--------------------------------

SELECT
    customer_id,
    COUNT(*) AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (
    SELECT visit_id FROM Transactions
    )
GROUP BY customer_id;

Share article

More articles

정규표현식 핵심 클래스

정규표현식 핵심 클래스

[SQL 문제 풀이] Sales Person (판매원)

[SQL 문제 풀이] Sales Person (판매원)

[JAVA 문제 풀이] 294. n^2 배열 자르기

[JAVA 문제 풀이] 294. n^2 배열 자르기

[JAVA 문제 풀이] 293. 지폐 접기

[JAVA 문제 풀이] 293. 지폐 접기

stupefyee

RSS·Powered by Inblog