Customer Who Visited but Did Not Make Any Transactions

Customer Who Visited but Did Not Make Any Transactions - Problem

Database Easy

Imagine you're a data analyst for a bustling shopping mall, and you need to identify customers who window shop without making any purchases!

You have two important datasets:

Visits Table: Records every time a customer enters the mall
Transactions Table: Records only the visits where customers made purchases

Your task is to find customers who visited the mall but never made any transactions during those visits, and count how many times each customer had these "non-purchasing" visits.

Goal: Return a list showing each customer's ID and their count of visits without transactions, helping the mall understand customer browsing patterns.

Example: If customer 123 visited 5 times but only made purchases during 2 visits, they should appear in your result with count = 3.

Input & Output

example_1.sql — Basic Case

$ Input: Visits: [[1,23],[2,9],[4,30],[5,54],[6,96],[7,54],[8,54]] Transactions: [[2,5,310],[3,5,300],[9,5,200],[12,1,910],[13,2,970]]

› Output: [[54,2],[30,1],[96,1]]

💡 Note: Customer 54 visited 3 times (visits 5,7,8) but made 0 transactions. Customer 30 visited 1 time (visit 4) with 0 transactions. Customer 96 visited 1 time (visit 6) with 0 transactions. Customer 23 and 9 made transactions so they're excluded.

example_2.sql — All Customers Make Transactions

$ Input: Visits: [[1,23],[2,9]] Transactions: [[1,1,100],[2,2,200]]

› Output: []

💡 Note: Every visit has a corresponding transaction, so no customers visited without making purchases.

example_3.sql — No Transactions At All

$ Input: Visits: [[1,23],[2,9],[3,23]] Transactions: []

› Output: [[23,2],[9,1]]

💡 Note: No transactions exist, so all visits are counted. Customer 23 visited twice, customer 9 visited once.

Visualization

Tap to expand

Understanding the Visualization

Collect Entry Logs

Gather all customer visit records from mall entrance

Match with Purchase Records

Use LEFT JOIN to match visits with actual purchase transactions

Identify Window Shoppers

Find visits with NULL transactions (no purchases made)

Count Browsing Sessions

Group by customer and count their non-purchasing visits

Key Takeaway

🎯 Key Insight: LEFT JOIN naturally handles the 'find what's missing' pattern - it shows all visits and marks missing transactions as NULL, making it easy to count non-purchasing visits per customer.

Time & Space Complexity

Time Complexity

⏱️

O(V × T)

For each visit V, we scan through all transactions T to check for matches

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result storage

✓ Linear Space

Constraints

1 ≤ visits.length ≤ 10⁵
transactions.length ≤ 10⁵
1 ≤ visit_id, transaction_id ≤ 10⁶
1 ≤ customer_id ≤ 10⁴
All visit_id and transaction_id are unique

Asked in

a Amazon 45 f Meta 32 G Google 28 ⊞ Microsoft 22

The optimal approach uses a LEFT JOIN between Visits and Transactions tables on visit_id. This preserves all visits and shows NULL for missing transactions. Filter WHERE transaction_id IS NULL to find visits without purchases, then GROUP BY customer_id and count. Time complexity is O(V + T) with proper indexing.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Subqueries)	O(V × T)	O(1)	Check each visit individually against all transactions
LEFT JOIN Approach (Optimal)	O(V + T)	O(V + T)	Use LEFT JOIN to find visits without matching transactions

Brute Force (Nested Subqueries) — Algorithm Steps

Select all visits from the Visits table
For each visit, check if its visit_id exists in the Transactions table
If no matching transaction found, include this visit
Group by customer_id and count the non-transaction visits

Visualization

Tap to expand

Step-by-Step Walkthrough

Select Visit

Pick a visit from the Visits table

Scan All Transactions

Check every row in Transactions table for matching visit_id

Mark as No-Transaction

If no match found, count this visit

Code -

solution.c — C

// Brute force SQL query
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = 
    "SELECT "
    "    customer_id, "
    "    COUNT(*) as count_no_trans "
    "FROM Visits v "
    "WHERE NOT EXISTS ( "
    "    SELECT 1 "
    "    FROM Transactions t "
    "    WHERE t.visit_id = v.visit_id "
    ") "
    "GROUP BY customer_id "
    "ORDER BY customer_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(V × T)

For each visit V, we scan through all transactions T to check for matches

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result storage

✓ Linear Space

Constraints

1 ≤ visits.length ≤ 10⁵
transactions.length ≤ 10⁵
1 ≤ visit_id, transaction_id ≤ 10⁶
1 ≤ customer_id ≤ 10⁴
All visit_id and transaction_id are unique

42.3K Views

High Frequency

~8 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Nested Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler