Implement Left Join Using Python Dictionaries Efficiently
Company: Citadel
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Orders
+---------+----------+--------+
| order_id| customer | amount |
+---------+----------+--------+
| 101 | C1 | 250 |
| 102 | C2 | 300 |
| 103 | C3 | 150 |
Customers
+----------+-----------+
| customer | city |
+----------+-----------+
| C1 | Seattle |
| C3 | Boston |
| C4 | Austin |
##### Scenario
Performing a left join in pure Python without external libraries.
##### Question
Write Python code (no third-party packages) to left-join two lists of dictionaries on key "customer"; discuss an O(N+M) hashing solution.
##### Hints
Contrast nested loops with dict-based look-ups; handle missing matches gracefully.
Overview: This question evaluates competency in tabular data manipulation and join semantics using Python dictionaries, emphasizing understanding of hashing-based lookups and algorithmic complexity such as O(N+M).
You are given two tables: Orders and Customers. Write a SQL query to perform a LEFT JOIN from Orders to Customers on the "customer" key, returning all orders along with the customer's city when available. If a customer is missing from Customers, the city should be NULL.
Return columns: order_id, customer, amount, city.
Tables
orders(order_id INT, customer VARCHAR(10), amount INT)
customers(customer VARCHAR(10), city VARCHAR(50))
Hints
- Use a LEFT JOIN to keep all rows from orders.
- Join condition is orders.customer = customers.customer.