Customer Who Visited but Did Not Make Any Transactions - Solution & Explanation
Problem Statement
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.
Approach Overview
Problem Overview: The database contains two tables: Visits and Transactions. Each visit has a visit_id, and transactions reference that visit. The task is to return each customer_id that visited the store but did not complete any transaction, along with how many such visits they made.
Approach 1: SQL Join and Group By Method (O(n) time, O(1) extra space)
This approach uses a LEFT JOIN between the Visits table and the Transactions table on visit_id. A left join keeps every visit even when no matching transaction exists. After the join, filter rows where transaction_id IS NULL, which indicates the visit produced no purchase. Finally, group results by customer_id and count those visits. The key insight is that unmatched rows from a left join naturally represent visits without transactions, making the query both readable and efficient.
This technique relies on fundamental relational database operations such as joins and aggregation. If you're practicing SQL interview questions, this pattern appears frequently in database and SQL join problems where missing relationships must be detected.
Approach 2: SQL Subquery with NOT EXISTS (O(n) time, O(1) extra space)
Another clean solution uses a correlated subquery with NOT EXISTS. Iterate through each row in Visits and check whether a matching row exists in Transactions for the same visit_id. If no row exists, that visit represents a customer who left without making a purchase. After filtering these visits, group by customer_id and count them.
The advantage of NOT EXISTS is its semantic clarity: the query explicitly states that no transaction should exist for that visit. Query planners in modern databases often optimize this pattern efficiently. This approach commonly appears in SQL subquery interview problems where the goal is to filter rows based on the absence of related data.
Recommended for interviews: The LEFT JOIN + GROUP BY solution is typically what interviewers expect. It demonstrates comfort with joins, null filtering, and aggregation in a single query. Showing the NOT EXISTS alternative is valuable because it proves you understand multiple ways to detect missing relationships in relational databases.
Approach 1: SQL Join and Group By Method
This approach utilizes SQL LEFT JOIN to find all visits that do not have corresponding transactions and GROUP BY to count the occurrences.
Since C is a lower-level language and does not natively support SQL operations, using a library like SQLite3 allows you to perform database operations. Correct database connections and execution functions are required to run the query. The SQL query will filter and group data appropriately within the database.
Complexity
Time Complexity: O(N + M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table.
Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
Approach 2: SQL Subquery and NOT EXISTS
This approach uses a subquery with NOT EXISTS to find all visits by customers that do not appear in the Transactions table.
For low-level languages like C, a library such as SQLite3 offers the capability to execute SQL queries. By utilizing subqueries and the NOT EXISTS clause, it's possible to efficiently filter based on the absence of transactions.
Complexity
Time Complexity: O(N * M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table.
Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
Approach 3: Subquery + Grouping
We can use a subquery to first find all visit_ids that have not made any transactions, and then group by customer_id to count the number of times each customer has not made any transactions.
Code
MySQL
Approach 4: Left Join + Grouping
We can also use a left join to join the Visits table and the Transactions table on visit_id, and then filter out the records where amount is NULL. After that, we can group by customer_id to count the number of times each customer has not made any transactions.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| SQL Join and Group By Method | Time Complexity: O(N + M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table. |
| SQL Subquery and NOT EXISTS | Time Complexity: O(N * M), where N is the number of rows in the Visits table and M is the number of rows in the Transactions table. |
| Subquery + Grouping | — |
| Left Join + Grouping | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + GROUP BY | O(n) | O(1) | Most common SQL pattern for detecting missing matches between two tables |
| NOT EXISTS Subquery | O(n) | O(1) | When you want explicit logic that filters rows lacking related records |
Video Solution
8. Customer Who Visited but Did Not Make Any Transactions | SQL Leetcode • Start Practicing • 27,460 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Customer Who Visited but Did Not Make Any Transactions easy or hard?
Customer Who Visited but Did Not Make Any Transactions Python/Java solution
How to solve Customer Who Visited but Did Not Make Any Transactions in O(n)?
What is the best approach for Customer Who Visited but Did Not Make Any Transactions?
Is Customer Who Visited but Did Not Make Any Transactions asked at Google/Amazon/Meta?
What data structure is used in Customer Who Visited but Did Not Make Any Transactions?
What is the time complexity of Customer Who Visited but Did Not Make Any Transactions?
Ready to solve this problem?
Practice Customer Who Visited but Did Not Make Any Transactions with our built-in code editor and test cases.
Practice on FleetCode