Customer Who Visited but Did Not Make Any Transactions (LeetCode 1581)

This chapter covers the step-by-step resolution of LeetCode Problem 1581 ("Customer Who Visited but Did Not Make Any Transactions"), breaking down how to combine relational tables, filter null records using outer joins, and aggregate categorical counts.

1. Problem Statement and Objectives

The objective is to find the IDs of all users who made a mall visit without making any accompanying transactions, alongside the exact number of times each such visit occurred.

• Input Tables:
  • Visits: Contains information about customer visits (visit_id as the primary key, and customer_id).
  • Transactions: Contains information about transactions made during visits (transaction_id as the primary key, visit_id, and amount).
• Output Requirements:
  • customer_id of users who visited without making transactions.
  • Count of such visits for each respective customer.
  • The result table can be sorted in any order.

2. SQL Architecture & Component Identification

When inspecting requirements like those in LeetCode 1581, certain patterns in the problem statement immediately dictate the structural components of the SQL query:

3. Data Schema and Example Walkthrough

Let us review sample tables to understand how records map between the two relations.

Sample Data

Visits Table:

Transactions Table:

Tracing the Matching Process

• Visit ID 1 (customer_id: 23): Has a transaction (transaction_id = 3, 13). Exclude.
• Visit ID 2 (customer_id: 9): Has a transaction (transaction_id = 12). Exclude.
• Visit ID 4 (customer_id: 30): Has no matching transaction in the Transactions table. Include.
• Visit ID 5 (customer_id: 54): Has a transaction (transaction_id = 2, 9). However, visit IDs 7 and 8 for customer 54 have no transactions. Include (Count = 2).
• Visit ID 6 (customer_id: 96): Has no matching transaction. Include (Count = 1).

Expected Output:

4. The Mental Model: Why Left Join?

To isolate visits that lack a corresponding transaction, an INNER JOIN would drop any visit that doesn't appear in the Transactions table. However, we specifically want to keep those unmatched visits.

Real-Life Analogy: Imagine keeping a master registry of store visitors (Visits) and a separate cash register receipt log (Transactions). If we want to find out who walked around the store but never stopped at the cash register, we lay the receipt log next to the visitor registry. For every visitor who didn't make a purchase, their receipt line will be completely blank (NULL).

Using a LEFT JOIN with Visits as the left table ensures that all visits are preserved, and missing transaction details are automatically populated with NULL values by the SQL engine.

5. Step-by-Step Query Construction

Let's build the SQL query methodically, starting from the data source and working our way to the select clauses and aggregations.

Step 1: Setting up the FROM and JOIN clauses

FROM Visits v
LEFT JOIN Transactions t
  ON v.visit_id = t.visit_id

Step 2: Filtering for Missing Transactions (WHERE)

Since an unmatched LEFT JOIN populates missing columns with NULL, we target rows where the transaction ID is null:

WHERE t.transaction_id IS NULL

Step 3: Selecting Columns and Applying Aggregation

We need to select the customer ID and count how many times each customer appears in our filtered set:

SELECT 
    v.customer_id, 
    COUNT(v.visit_id) AS count_no_trans

Step 4: Grouping the Results (GROUP BY)

Whenever we use an aggregate function like COUNT(), we must group our non-aggregated columns using GROUP BY:

GROUP BY v.customer_id

6. Complete Production-Ready SQL Query

Here is the complete, optimized query that successfully passes all test cases:

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

7. Critical SQL Rules Demonstrated in This Problem

When writing aggregation queries in standard SQL databases (MySQL, PostgreSQL, SQL Server), keep these two foundational rules in mind:

1. The Aggregation & Grouping Rule: Whenever an aggregate function (COUNT(), SUM(), MAX(), MIN(), AVG()) is used, any column included in the SELECT clause that is not wrapped inside an aggregate function must be listed in the GROUP BY clause.
2. The Left Join Null Behavior: When performing a LEFT JOIN, records in the left table that have no match in the right table will have NULL values assigned to all columns of the right table. This makes WHERE right_table.primary_key IS NULL the industry-standard idiom for finding "anti-joins" (records existing in Table A but not in Table B).

Summary and Key Takeaways

• Problem Recognition: Identifying keywords like "without making any transaction" indicates an anti-join pattern solved via LEFT JOIN and IS NULL.
• Primary vs. Foreign Keys: visit_id serves as the relational bridge connecting the Visits and Transactions tables.
• Preserving Unmatched Records: A LEFT JOIN retains all customer visits, turning missing financial records into explicit NULL entries.
• Filtering with WHERE: Checking t.transaction_id IS NULL isolates visits that resulted in zero purchases.
• Aggregation with COUNT(): Combining COUNT() with GROUP BY customer_id compiles the total frequency of non-transactional visits per user.
• Aliasing Output Columns: Using AS count_no_trans ensures the returned result matches the exact column naming expectations of the evaluation test suite.