Customers Who Never Order - Practice Coding Problems

Customers Who Never Order - Problem

Database Easy

Imagine you're running a business and want to identify inactive customers who might need a marketing push! 📈

You have two database tables:

Customers Table
id (int)	name (varchar)
Each row contains a customer's ID and name

Orders Table
id (int)	customerId (int)
Each row contains an order ID and the customer who placed it

Your mission: Write a SQL query to find all customers who have never placed an order. These are potential customers you could target with special promotions!

The result should return customer names in any order.

Example:
If Alice and Bob are in the Customers table, but only Alice has orders in the Orders table, then Bob should be returned as he never ordered anything.

Input & Output

example_1.sql — Basic Example

$ Input: Customers: [(1,'Alice'), (2,'Bob'), (3,'Charlie')]\nOrders: [(101,1), (102,1), (103,3)]

› Output: [('Bob')]

💡 Note: Alice has orders 101 and 102, Charlie has order 103, but Bob has no orders, so only Bob is returned.

example_2.sql — All Customers Have Orders

$ Input: Customers: [(1,'Alice'), (2,'Bob')]\nOrders: [(101,1), (102,2)]

› Output: []

💡 Note: Both Alice and Bob have at least one order, so no customers are returned.

example_3.sql — No Orders Table

$ Input: Customers: [(1,'Alice'), (2,'Bob'), (3,'Charlie')]\nOrders: []

› Output: [('Alice'), ('Bob'), ('Charlie')]

💡 Note: No orders exist, so all customers are returned as they have never ordered anything.

Visualization

Tap to expand

Understanding the Visualization

Customer Registry

You have a list of all customers who signed up for your loyalty program

Purchase History

You also have records of all purchases made by customer ID

LEFT JOIN Magic

Connect each customer to their purchases, but keep customers even if they have no purchases

Find the Gaps

Customers with NULL purchase records are the ones who never bought anything

Key Takeaway

🎯 Key Insight: LEFT JOIN preserves all customers while identifying those without orders through NULL values - it's like keeping everyone on the guest list but highlighting who didn't show up to the party!

Time & Space Complexity

Time Complexity

⏱️

O(n × m)

For each of n customers, we potentially scan m orders

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ customers.length ≤ 10⁴
1 ≤ orders.length ≤ 10⁶
Customer IDs are unique in Customers table
Order IDs are unique in Orders table
customerId in Orders table references id in Customers table

Asked in

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

The optimal solution uses LEFT JOIN to combine the Customers and Orders tables, keeping all customers even if they have no orders. By filtering for NULL values in the Orders table, we efficiently identify customers who never placed an order. This approach is more efficient than subqueries as it requires only a single pass through the data.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery Approach (NOT EXISTS)	O(n × m)	O(1)	Check each customer individually to see if they have any orders
LEFT JOIN Approach (Optimal)	O(n + m)	O(n + m)	Use LEFT JOIN to combine tables and filter for NULL matches

Subquery Approach (NOT EXISTS) — Algorithm Steps

Iterate through each customer in the Customers table
For each customer, check if their ID exists in the Orders table
If no orders found, include them in the result
Return customer names who have no orders

Visualization

Tap to expand

Step-by-Step Walkthrough

Start with Customers

Begin with all customers in the table

Check Each Customer

For each customer, run subquery to find orders

Filter Results

Keep only customers with no matching orders

Code -

solution.c — C

// SQL Query using NOT EXISTS subquery
#include <stdio.h>
#include <string.h>

const char* getQuery() {
    return "SELECT c.name AS Customers "
           "FROM Customers c "
           "WHERE NOT EXISTS ("
           "    SELECT 1 "
           "    FROM Orders o "
           "    WHERE o.customerId = c.id"
           ")";
}

Time & Space Complexity

Time Complexity

⏱️

O(n × m)

For each of n customers, we potentially scan m orders

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

Constraints

1 ≤ customers.length ≤ 10⁴
1 ≤ orders.length ≤ 10⁶
Customer IDs are unique in Customers table
Order IDs are unique in Orders table
customerId in Orders table references id in Customers table

67.5K Views

High Frequency

~15 min Avg. Time

2.2K 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

Subquery Approach (NOT EXISTS) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler