Customer Who Visited but Did Not Make Any Transactions

Customer Who Visited but Did Not Make Any Transactions

What is this problem about?

The Customer Who Visited but Did Not Make Any Transactions interview question focuses on finding "lost opportunities" in a retail or service environment. You are given a Visits table and a Transactions table. You need to find the IDs of customers who visited the store but have no corresponding entry in the transactions table, along with the count of such visits. This Customer Who Visited but Did Not Make Any Transactions coding problem is a classic example of identifying records in one table that are missing from another.

Why is this asked in interviews?

Tech companies like Google, Meta, and Adobe ask this to test basic knowledge of "Anti-Joins." It evaluates if you understand how to use LEFT JOIN with a WHERE ... IS NULL check, or if you prefer using NOT IN or NOT EXISTS. It’s a core skill for generating "exception reports" in business intelligence.

Algorithmic pattern used

This follows the Database interview pattern of filtering by exclusion.

1. Join approach: Perform a LEFT JOIN from Visits to Transactions on visit_id.
2. Filter: Filter for rows where the transaction_id (from the right table) is NULL.
3. Aggregate: Group by customer_id and count the number of visits.

Example explanation

Visit Log:

• Visit 1: Customer 5
• Visit 2: Customer 5
• Visit 3: Customer 6 Transaction Log:
• Trans 101: Visit 1 (Customer 5) In this case, Customer 5's first visit resulted in a sale, but their second visit did not. Customer 6's only visit (Visit 3) had no sale. Result: Customer 5 (1 visit without transaction), Customer 6 (1 visit without transaction).

Common mistakes candidates make

• Inner Join: Using a standard JOIN, which only returns visitors who did make a transaction, the exact opposite of what was asked.
• Counting the wrong ID: Using COUNT(transaction_id) which would return 0 for these users. You must count visit_id or use COUNT(*).
• Performance with NOT IN: Using NOT IN on a column that contains NULL values, which in SQL can lead to an empty result set due to three-valued logic.

Interview preparation tip

Always default to LEFT JOIN or NOT EXISTS for exclusion problems. Avoid NOT IN unless you are certain the subquery will never return a NULL, as it is a common pitfall in production SQL.