Customer Who Visited but Did Not Make Any Transactions - Problem

You are given two tables: Visits and Transactions.

The Visits table contains information about customers who visited the mall, with each visit having a unique visit_id and associated customer_id.

The Transactions table contains information about transactions made during visits, linking each transaction to a specific visit_id.

Task: Find the IDs of customers who visited the mall but did not make any transactions, along with the count of such visits for each customer.

Return the result table sorted in any order.

Table Schema

Visits
Column Name Type Description
visit_id PK int Unique identifier for each visit
customer_id int ID of the customer who made the visit
Primary Key: visit_id
Transactions
Column Name Type Description
transaction_id PK int Unique identifier for each transaction
visit_id int Foreign key linking to the visit when transaction occurred
amount int Transaction amount
Primary Key: transaction_id

Input & Output

Example 1 — Basic Scenario
Input Tables:
Visits
visit_id customer_id
1 23
2 9
4 30
12 23
Transactions
transaction_id visit_id amount
2 12 20
Output:
customer_id count_no_trans
9 1
30 1
💡 Note:

Customer 23 made 2 visits (visit_id 1 and 12), but only visit 12 resulted in a transaction. Since we need customers with only non-transaction visits, customer 23 is excluded. Customers 9 and 30 each made 1 visit without any transactions, so they appear in the result.
Example 2 — Multiple Non-Transaction Visits
Input Tables:
Visits
visit_id customer_id
1 54
2 54
3 54
Transactions
transaction_id visit_id amount
Output:
customer_id count_no_trans
54 3
💡 Note:

Customer 54 visited 3 times (visits 1, 2, and 3) but never made any transactions. The result shows customer 54 with 3 non-transaction visits.
Example 3 — All Visits Have Transactions
Input Tables:
Visits
visit_id customer_id
1 23
2 9
Transactions
transaction_id visit_id amount
1 1 100
2 2 50
Output:
customer_id count_no_trans
💡 Note:

Every visit resulted in a transaction, so no customers have non-transaction visits. The result is empty.

Constraints

  • 1 ≤ visit_id ≤ 100
  • 1 ≤ customer_id ≤ 100
  • 1 ≤ transaction_id ≤ 100
  • 1 ≤ amount ≤ 1000

Visualization

Tap to expand
Finding Customers Who Visited Without TransactionsInput: Visits & TransactionsVisitsTransactionsvisit_id: 1,2,4,12customer: 23,9,30,23trans_id: 2visit_id: 12amount: 20Only visit_id 12 has transactionLEFT JOIN+ WHERE NULLOutputcustomer_idcount_no_trans91301Customers with visits but no transactionsKey Insight: LEFT JOIN preserves all visits, NULL indicates missing transactionsGROUP BY customer_id counts non-purchasing visits per customer
Understanding the Visualization
1
Input Tables
Visits table and Transactions table
2
LEFT JOIN
Join on visit_id, preserve all visits
3
Filter & Count
WHERE transaction_id IS NULL, GROUP BY customer
Key Takeaway
🎯 Key Insight: Use LEFT JOIN to find missing relationships between tables and GROUP BY to aggregate counts

Related Problems

Asked in
Amazon 15 Facebook 12 Google 8
23.5K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen