Sponsored
Sponsored
The goal is to find customers who never made an order. By using a LEFT JOIN between the Customers and Orders tables, we can identify which customer IDs do not appear in the Orders table. We will check for NULL entries in the Orders table after the join to find these customers.
Time Complexity: O(n), where n is the number of rows in the Customers table.
Space Complexity: O(n), primarily for storing the result set.
1import pandas as pd
2
3# Sample data
4customers_data = {'id': [1, 2, 3, 4], 'name': ['Joe', 'Henry', 'Sam', 'Max']}
5orders_data = {'id': [1, 2], 'customerId': [3, 1]}
6
7# Creating DataFrame
8df_customers = pd.DataFrame(customers_data)
9df_orders = pd.DataFrame(orders_data)
10
11# Performing the left join
12result = pd.merge(df_customers, df_orders, left_on='id', right_on='customerId', how='left')
13
14# Selecting customers who never ordered
15never_ordered = result[result['customerId'].isnull()]['name'].tolist()
16print(never_ordered) # Output: ['Henry', 'Max']This solution uses Python's pandas library to replicate the SQL LEFT JOIN operation. We first create DataFrames for the customers and orders data. Then, a LEFT JOIN is performed where the left DataFrame is df_customers and we match it based on 'id' with 'customerId' of df_orders. After merging, we filter the entries where 'customerId' is NaN (indicating no matching order), and collect the corresponding customer names.
This approach uses a subquery to first identify the list of customers who have placed orders. Then, it excludes these customers from the list of all customers to find those who haven't placed any orders.
Time Complexity: O(n + m), with n as the number of customers and m as the number of orders.
Space Complexity: O(1) for the operation itself, but the result set storage would be O(k), where k is the number of customers who have never ordered.
1SELECT name AS Customers FROM Customers WHERE id NOT IN (This SQL solution uses a subquery to gather all customerId entries from the Orders table. The primary query selects customers from the Customers table whose id is not in the previously selected list, effectively filtering out customers who have placed an order.