Watch 10 video solutions for Customer Who Visited but Did Not Make Any Transactions, a easy level problem involving Database. This walkthrough by Start Practicing has 24,478 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
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 | 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 |
