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
Customer Who Visited but Did Not Make Any Transactions INPUT Visits Table: visit_id cust_id 1 23 2 9 4 30 5 54 6 96 Transactions: trans_id visit 12 1 13 2 9 5 Customers visiting mall: 23 9 30 54 96 5 total visits recorded 3 visits have transactions ALGORITHM STEPS 1 LEFT JOIN Join Visits with Transactions on visit_id 2 FILTER NULL WHERE transaction_id IS NULL Keep visits with no purchase 3 GROUP BY Group by customer_id to aggregate visits 4 COUNT COUNT(*) as count_no_trans Count visits per customer SELECT customer_id, COUNT(*) count_no_trans FROM Visits v LEFT JOIN Transactions t WHERE t.trans_id IS NULL FINAL RESULT customer_id count 30 1 96 1 Analysis: 30 Visit 4: No transaction 96 Visit 6: No transaction Customers with transactions: 23 9 54 OK - These made purchases 2 customers, each with 1 no-trans visit Key Insight: LEFT JOIN keeps all visits even without matching transactions. When transaction_id IS NULL, it means the customer visited but made no purchase. GROUP BY aggregates multiple such visits per customer, and COUNT(*) gives us the number of no-transaction visits for each customer. TutorialsPoint - Customer Who Visited but Did Not Make Any Transactions | Optimal Solution (LEFT JOIN + GROUP BY)

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