UptoSkills Logo
Login
Customers with No OrdersMedium

Customers with No Orders

MediumSQLPractice library · +20 XPanti joinsqlleft join

A company maintains a list of its customers and a record of all orders placed. We need to identify customers who are registered but have never made a purchase. This is crucial for targeted marketing campaigns to re-engage inactive users.

Tables:

  • customers: customer_id (INT, PK), customer_name (VARCHAR(100))
  • orders: order_id (INT, PK), customer_id (INT, FK to customers.customer_id), order_date (DATE), order_amount (NUMERIC(10,2))

Return the customer_id and customer_name for all customers who have no corresponding entries in the orders table. The results should be ordered by customer_id.

Handle NULLs: Assume customer_id in orders is NOT NULL. Empty results are possible.

Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date DATE NOT NULL,
    order_amount NUMERIC(10,2) NOT NULL
);

Example data

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David'),
(5, 'Eve');

INSERT INTO orders (order_id, customer_id, order_date, order_amount) VALUES
(101, 1, '2023-01-15', 150.50),
(102, 2, '2023-02-20', 75.00),
(103, 1, '2023-03-10', 200.00),
(104, 3, '2023-04-05', 50.25),
(105, 4, '2023-05-18', 300.00);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems