50 SQL LEFT JOIN Questions for Data Engineering Interviews
Sample Tables with Data
Customers Table:
| CustomerID | CustomerName | Email | City | Country |
|------------|---------------|------------------|-----------|---------|
|3 | Charlie | [email protected]| Bangalore | India |
Orders Table:
| OrderID | CustomerID | OrderDate | OrderAmount |
|---------|------------|------------|-------------|
| 101 |1 | 2024-01-10 | 500.00 |
| 102 |1 | 2024-02-15 | 250.00 |
| 103 |2 | 2024-03-05 | 800.00 |
| 104 |3 | 2024-03-10 | 300.00 |
| 105 |1 | 2024-04-01 | 100.00 |
Payments Table:
| PaymentID | CustomerID | PaymentDate | PaymentAmount |
|-----------|------------|-------------|----------------|
| 201 |1 | 2024-01-15 | 500.00 |
| 202 |2 | 2024-03-06 | 800.00 |
| 203 |3 | 2024-03-11 | 300.00 |
| 204 |4 | 2024-04-12 | 200.00 |
OrderDetails Table:
| OrderDetailID | OrderID | ProductID | Quantity |
50 SQL LEFT JOIN Questions for Data Engineering Interviews
|---------------|---------|-----------|----------|
| 1001 | 101 | 301 |2 |
| 1002 | 102 | 302 |1 |
| 1003 | 103 | 303 |5 |
| 1004 | 105 | 302 |3 |
Products Table:
| ProductID | ProductName | Price |
|-----------|---------------|-------|
| 301 | Keyboard | 250 |
| 302 | Mouse | 100 |
| 303 | Monitor | 800 |
| 304 | USB-C Cable | 50 |
Customers & Orders
1. Show all customers with their order details.
2. List customers who have not placed any orders.
3. Show each customer's name and total number of orders.
4. Display all customers with their most recent order date.
5. Show customers and their total order amount (NULL if no orders).
6. List all customers and count of orders grouped by country.
7. List customers who placed an order in March (show all customers).
8. Show all orders and include customers who didn't place any.
9. List each order with corresponding customer name.
10. Show all customers and order IDs, even if they have no orders.
Customers & Payments
1. List customers with their last payment date.
2. Find customers who haven't made any payments.
3. Show all customer emails and their payment amounts.
50 SQL LEFT JOIN Questions for Data Engineering Interviews
4. Display customers along with payment count.
5. Show customers and total payments made (0 if none).
6. List each customer's city and their latest payment info.
7. Show customers and payment amounts made in April.
8. Find customers who made payment but no order.
9. Show customer names with payments greater than 300.
10. Display customers along with payments and orders (nested LEFT JOIN).
Orders & OrderDetails
1. List all orders with their products (include orders without products).
2. Find orders without any order details.
3. Show order amount along with total quantity ordered.
4. Display all orders and number of items (use LEFT JOIN + COUNT).
5. List order IDs and corresponding product names.
6. Show all orders with NULL in product ID (if product is missing).
7. Get all product-wise order quantities, include those never ordered.
8. List customers and products they have ordered (use JOINs).
9. Show orders and include missing/malformed order details.
10. Find average quantity per order including those with no details.
Products & OrderDetails
1. Show all products and how many times each was ordered.
2. Find products that have never been ordered.
3. Display total revenue generated per product (include 0 if none).
4. List product names and associated order IDs.
5. Show all products and average quantity ordered.
6. List product price and total quantity ordered.
7. Display products and number of distinct customers who ordered them.
8. Show all products ordered in Jan, even if never ordered.
9. List products with maximum quantity in any single order.
50 SQL LEFT JOIN Questions for Data Engineering Interviews
10. Show all order detail records with product name.
Mixed LEFT JOIN Chains
1. Show all customers, their orders, and payment info (LEFT JOIN chain).
2. List all order IDs, product names, and customer names.
3. Find all orders and their products, even if no product exists.
4. Show customer, order, and product details in one query.
5. Display customers and their last order amount & payment date.
6. Get a full customer purchase history (orders + products + payments).
7. List customers who either ordered or paid (even if only one).
8. Show customers who ordered a specific product (e.g. Monitor), include those who didnt.
9. Display customer names and whether they made a payment after an order.
10. Find mismatches: customers who have payments but no orders.
