1581. Customer Who Visited but Did Not Make Transactions

The problem

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.

Python

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('your_database.db')
cursor = connection.cursor()

# Execute the SQL query
query = '''
SELECT customer_id, COUNT(visit_id) AS count_no_trans
FROM Visits
LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id
WHERE Transactions.transaction_id IS NULL
GROUP BY customer_id;'''

cursor.execute(query)
rows = cursor.fetchall()

# Output the results
for row in rows:
    print(f"customer_id: {row[0]}, count_no_trans: {row[1]}")

# Close the connection
connection.close()

Java

// Java with JDBC
import java.sql.*;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:your_database.db";
        String query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id WHERE Transactions.transaction_id IS NULL GROUP BY customer_id;";
        
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                System.out.println("customer_id: " + rs.getInt("customer_id") + ", count_no_trans: " + rs.getInt("count_no_trans"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

C++

// C++ with a database connection
#include <iostream>
#include <string>
#include <sqlite3.h>

void executeSQL(const std::string &query) {
    sqlite3 *db;
    char *zErrMsg = nullptr;
    int rc;

    rc = sqlite3_open("your_database.db", &db);
    if(rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        return;
    }

    rc = sqlite3_exec(db, query.c_str(), callback, 0, &zErrMsg);
    if(rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    sqlite3_close(db);
}

int main() {
    std::string query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id WHERE Transactions.transaction_id IS NULL GROUP BY customer_id;";
    executeSQL(query);
    return 0;
}

// Note: Make sure to implement the callback function and handle the database initialization and cleanup properly. The query provided is SQL and should be run on a SQL-compliant database like SQLite3.

JavaScript

// JavaScript using node-sqlite3
const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database('./your_database.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the SQlite database.');
});

let sql = `
SELECT customer_id, COUNT(visit_id) AS count_no_trans
FROM Visits
LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id
WHERE Transactions.transaction_id IS NULL
GROUP BY customer_id;`;

// Execute SQL query
let customerVisits = db.all(sql, [], (err, rows) => {
    if (err) {
        throw err;
    }
    rows.forEach((row) => {
        console.log(`${row.customer_id} - ${row.count_no_trans}`);
    });
});

db.close((err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Close the database connection.');
});

// C does not directly handle SQL queries, but you can use a library like SQLite3 in C to execute the SQL queries.

using System;
using System.Data;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=your_database.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits LEFT JOIN Transactions ON Visits.visit_id = Transactions.visit_id WHERE Transactions.transaction_id IS NULL GROUP BY customer_id;";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"customer_id: {reader["customer_id"]}, count_no_trans: {reader["count_no_trans"]}");
                    }
                }
            }
        }
    }
}

This Python solution utilizes the sqlite3 library to run an SQL query that joins Visits and Transactions tables, filters for null transactions, and groups the results. Python's sqlite3 is useful for embedding SQL queries in a Python script for easier data processing.

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.

Try this approach in the editor

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.

Python

import sqlite3

# Connect to SQLite database
connection = sqlite3.connect('your_database.db')
cursor = connection.cursor()

# Execute the SQL query
query = '''
SELECT customer_id, COUNT(visit_id) AS count_no_trans
FROM Visits
WHERE NOT EXISTS (SELECT 1 FROM Transactions WHERE Visits.visit_id = Transactions.visit_id)
GROUP BY customer_id;'''

cursor.execute(query)
rows = cursor.fetchall()

# Output the results
for row in rows:
    print(f"customer_id: {row[0]}, count_no_trans: {row[1]}")

# Close the connection
connection.close()

Java

// Java with JDBC
import java.sql.*;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:your_database.db";
        String query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits WHERE NOT EXISTS (SELECT 1 FROM Transactions WHERE Visits.visit_id = Transactions.visit_id) GROUP BY customer_id;";
        
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                System.out.println("customer_id: " + rs.getInt("customer_id") + ", count_no_trans: " + rs.getInt("count_no_trans"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

C++

// C++ with a database connection
#include <iostream>
#include <string>
#include <sqlite3.h>

void executeSQL(const std::string &query) {
    sqlite3 *db;
    char *zErrMsg = nullptr;
    int rc;

    rc = sqlite3_open("your_database.db", &db);
    if(rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        return;
    }

    rc = sqlite3_exec(db, query.c_str(), callback, 0, &zErrMsg);
    if(rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    sqlite3_close(db);
}

int main() {
    std::string query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits WHERE NOT EXISTS (SELECT * FROM Transactions WHERE Visits.visit_id = Transactions.visit_id) GROUP BY customer_id;";
    executeSQL(query);
    return 0;
}

// Note: Make sure to implement the callback function and handle the database initialization and cleanup properly. The query provided is SQL and should be run on a SQL-compliant database like SQLite3.

JavaScript

// JavaScript using node-sqlite3
const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database('./your_database.db', (err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Connected to the SQlite database.');
});

let sql = `
SELECT customer_id, COUNT(visit_id) AS count_no_trans
FROM Visits
WHERE NOT EXISTS (SELECT 1 FROM Transactions WHERE Visits.visit_id = Transactions.visit_id)
GROUP BY customer_id;`;

// Execute SQL query
let customerVisits = db.all(sql, [], (err, rows) => {
    if (err) {
        throw err;
    }
    rows.forEach((row) => {
        console.log(`${row.customer_id} - ${row.count_no_trans}`);
    });
});

db.close((err) => {
    if (err) {
        console.error(err.message);
    }
    console.log('Close the database connection.');
});

// C does not directly handle SQL queries, however, you can execute them using a library like SQLite3.

using System;
using System.Data;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=your_database.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();

            string query = "SELECT customer_id, COUNT(visit_id) AS count_no_trans FROM Visits WHERE NOT EXISTS (SELECT 1 FROM Transactions WHERE Visits.visit_id = Transactions.visit_id) GROUP BY customer_id;";
            using (SQLiteCommand command = new SQLiteCommand(query, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"customer_id: {reader["customer_id"]}, count_no_trans: {reader["count_no_trans"]}");
                    }
                }
            }
        }
    }
}

Utilizing Python with the sqlite3 library, this code performs a SQL query using subquery logic with NOT EXISTS to detect visits without matching transactions, yielding results grouped by customer_id. It demonstrates efficient database interaction and records processing.

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.

Try this approach in the editor

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.

MySQL

# Write your MySQL query statement below
SELECT customer_id, COUNT(1) AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (SELECT visit_id FROM Transactions)
GROUP BY 1;

Try this approach in the editor

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.

MySQL

# Write your MySQL query statement below
SELECT customer_id, COUNT(1) AS count_no_trans
FROM
    Visits
    LEFT JOIN Transactions USING (visit_id)
WHERE amount IS NULL
GROUP BY 1;

Try this approach in the editor

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. Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
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. Space Complexity: O(K), where K is the number of unique customer_id entries in the result set.
Subquery + Grouping	–
Left Join + Grouping	–

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

Frequently asked questions

Is Customer Who Visited but Did Not Make Any Transactions easy or hard?

The problem is rated Easy because it tests basic SQL skills: joining tables, identifying missing matches, and aggregating results. Anyone comfortable with LEFT JOIN and GROUP BY can solve it quickly once they recognize that visits without transactions appear as NULL values after the join.

Customer Who Visited but Did Not Make Any Transactions Python/Java solution

This is primarily a SQL database problem rather than a typical algorithm problem implemented in Python or Java. On platforms like LeetCode, you solve it by writing a SQL query using LEFT JOIN + GROUP BY or a NOT EXISTS subquery to filter visits without transactions.

How to solve Customer Who Visited but Did Not Make Any Transactions in O(n)?

Use a LEFT JOIN between Visits and Transactions on visit_id, then filter rows where transaction_id is NULL. Those rows represent visits that produced no purchase. Group the remaining rows by customer_id and count them to get the number of non-transaction visits per customer.

What is the best approach for Customer Who Visited but Did Not Make Any Transactions?

The most common solution uses a LEFT JOIN between the Visits and Transactions tables followed by a filter where transaction_id IS NULL. This identifies visits that never produced a transaction. After filtering, group by customer_id and count those visits. The query runs in O(n) time depending on indexing and is the approach most interviewers expect.

Is Customer Who Visited but Did Not Make Any Transactions asked at Google/Amazon/Meta?

SQL join and missing-record queries like this appear frequently in database interviews at companies such as Amazon, Meta, and other data-driven teams. Interviewers use them to test understanding of joins, NULL filtering, and aggregation with GROUP BY.

What data structure is used in Customer Who Visited but Did Not Make Any Transactions?

The problem relies on relational database tables and SQL operations rather than traditional data structures. The key concepts are table joins, grouping with GROUP BY, and filtering rows using conditions such as IS NULL or NOT EXISTS.

What is the time complexity of Customer Who Visited but Did Not Make Any Transactions?

Both common solutions run in O(n) time where n represents the number of visit records processed by the database engine. The join or subquery checks for matching transaction rows and then performs a grouping step. Space complexity is typically O(1) because SQL engines stream results without storing large intermediate structures.

Customer Who Visited but Did Not Make Any Transactions solution and explanation

The problem

Approach overview

Approach 1: SQL Join and Group By Method

Approach 2: SQL Subquery and NOT EXISTS

Approach 3: Subquery + Grouping

Approach 4: Left Join + Grouping

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Customer Who Visited but Did Not Make Any Transactions solution and explanation

The problem

Approach overview

Approach 1: SQL Join and Group By Method

Approach 2: SQL Subquery and NOT EXISTS

Approach 3: Subquery + Grouping

Approach 4: Left Join + Grouping

Complexity comparison

Complexity analysis

Frequently asked questions

Comments

Related problems