[Leetcode] Customer Who Visited but Did Not Make Any Transactions

문제 링크

정답

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 V.customer_id;

✅ Point

• 이런 식의 문제 유형을 “NOT IN” 이라고 함
  • 방법 1: NOT IN / EXISTS 로 풀기
  • 방법 2: LEFT JOIN + IS NULL 로 풀기
• Order Of Execution
  • FROM Visits > LEFT JOIN Transactions ~ > WHERE ~ > GROUP BY ~ > SELECT ~

다른 풀이

• NOT IN 으로 푼 풀이

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

• Order Of Execution
  • FROM Visits > (FROM Transactions > GROUP BY ~ > SELECT ~) > WHERE > GROUP BY > SELECT