183. Customers Who Never Order – Leetcode Solutions

Description

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID and name of a customer.

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
customerId is a foreign key (reference columns) of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

Write a solution to find all customers who never order anything.

Return the result table in any order.

The result format is in the following example.

Examples:

Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

Solution in Pandas

To solve this problem using Pandas, we need to identify customers who do not have any corresponding entries in the Orders table.

1. Merge the Customers and Orders DataFrames:
   • Perform a left join on the Customers DataFrame with the Orders DataFrame on the id column of Customers and customerId column of Orders.
2. Filter out customers with orders:
   • Identify rows where the customerId column in the merged DataFrame is NaN (indicating that the customer has no orders).
3. Select and format the output:
   • Extract the names of customers who meet the condition.

import pandas as pd

def find_customers(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    # Perform a left join on the 'Customers' and 'Orders' DataFrames
    merged_df = customers.merge(orders, left_on='id', right_on='customerId', how='left')
    
    # Filter out customers who have no orders (customerId is NaN in the merged DataFrame)
    no_orders_df = merged_df[merged_df['customerId'].isna()]
    
    # Select the 'name' column and rename it to 'Customers'
    result_df = no_orders_df[['name']].rename(columns={'name': 'Customers'})
    
    return result_df

Explanation of the code:

1. Merging DataFrames:
   • customers.merge(orders, left_on='id', right_on='customerId', how='left'): This performs a left join between the Customers and Orders DataFrames. The left_on='id' and right_on='customerId' ensure that we join on the customer IDs. The how='left' parameter ensures that all rows from the Customers DataFrame are included, along with matching rows from the Orders DataFrame (if any).
2. Filtering for customers without orders:
   • merged_df[merged_df['customerId'].isna()]: This filters the merged DataFrame to include only rows where the customerId from the Orders table is NaN, indicating that the customer has no orders.
3. Selecting and Renaming Columns:
   • no_orders_df[['name']].rename(columns={'name': 'Customers'}): This selects the name column from the filtered DataFrame and renames it to Customers to match the required output format.

Solution in MySQL

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.customerId IS NULL;

Solution in PostgreSQL

-- Select customers who do not have any orders
SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
-- Filter to include only customers with no corresponding orders
WHERE o.customerId IS NULL;