E-commerce Order Fulfillment Pipeline

Imagine you are working for an e-commerce company. To streamline order fulfillment, you need to extract comprehensive data about customer orders, including customer details, the products ordered, shipping information, and payment status. This requires joining information from several different tables in your database. This challenge will test your ability to construct complex SQL JOIN queries involving multiple tables.

Problem Description

You are tasked with generating a report that displays specific information about each customer order. This report needs to combine data from several tables: Customers, Orders, OrderItems, Products, Shipping, and Payments.

Key Requirements:

Retrieve the customer's full name, email address, and phone number.
For each order, retrieve the order ID, order date, and the total quantity of items ordered.
For each item within an order, retrieve the product name and its price at the time of the order.
Retrieve the shipping address and shipping status for each order.
Retrieve the payment method and payment status for each order.
The report should only include orders that have been successfully paid.

Expected Behavior:

The output should be a single result set where each row represents a distinct product within a paid order, linked back to its customer, shipping, and payment details. If an order contains multiple items, each item should appear on a separate row, but all rows for the same order should share the same customer, shipping, and payment information.

Edge Cases to Consider:

Customers who have not placed any orders.
Orders that may not have associated shipping or payment records (though for this challenge, we're focusing on paid orders, implying payment records).
Products that have been discontinued or removed from the Products table but are still present in OrderItems (assume referential integrity is maintained for this problem).

Examples

Example 1:

Input Tables (Simplified Representation):

Customers

customer_id	first_name	last_name	email	phone
101	Alice	Smith	alice@example.com	555-1234
102	Bob	Johnson	bob@example.com	555-5678

Orders

order_id	customer_id	order_date
1001	101	2023-10-26
1002	101	2023-10-27
1003	102	2023-10-27

OrderItems

order_item_id	order_id	product_id	quantity
1	1001	501	2
2	1001	502	1
3	1002	501	1
4	1003	503	3

Products

product_id	product_name	price
501	Laptop	1200
502	Wireless Mouse	25
503	Mechanical Keyboard	150

Shipping

shipping_id	order_id	address	shipping_status
2001	1001	123 Main St	Shipped
2002	1002	123 Main St	Processing
2003	1003	456 Oak Ave	Shipped

Payments

payment_id	order_id	payment_method	payment_status
3001	1001	Credit Card	Paid
3002	1002	PayPal	Paid
3003	1003	Credit Card	Pending

Output:

customer_full_name	customer_email	customer_phone	order_id	order_date	product_name	item_quantity	product_price	shipping_address	shipping_status	payment_method	payment_status
Alice Smith	alice@example.com	555-1234	1001	2023-10-26	Laptop	2	1200	123 Main St	Shipped	Credit Card	Paid
Alice Smith	alice@example.com	555-1234	1001	2023-10-26	Wireless Mouse	1	25	123 Main St	Shipped	Credit Card	Paid
Alice Smith	alice@example.com	555-1234	1002	2023-10-27	Laptop	1	1200	123 Main St	Processing	PayPal	Paid

Explanation: We join Customers with Orders on customer_id. Then, we join this result with OrderItems on order_id. Next, we join with Products on product_id to get product details. We also join with Shipping on order_id and Payments on order_id. Finally, we filter to include only orders where payment_status is 'Paid'. Order ID 1003 is excluded because its payment_status is 'Pending'.

Example 2: (Order with a single item)

Input: (Same tables as Example 1)

Output: (Same as Example 1, as Order 1003 was not paid)

Explanation: This example reiterates the logic from Example 1. If an order had only one item, it would still appear as a single row for that item.

Example 3: (Customer with no paid orders)

Input: Assume Customers table also contains:

customer_id	first_name	last_name	email	phone
103	Charlie	Brown	charlie@example.com	555-9999

And Orders table has:

order_id	customer_id	order_date
1004	103	2023-10-28

And OrderItems has:

order_item_id	order_id	product_id	quantity
5	1004	501	1

And Payments table has:

payment_id	order_id	payment_method	payment_status
3004	1004	PayPal	Pending

Output: (An empty result set, or no rows corresponding to customer Charlie Brown)

Explanation: Customer Charlie Brown has an order (1004), but since the payment status for this order is 'Pending', it is not included in the final report, demonstrating the filtering by payment status.

Constraints

The database contains at least the following tables: Customers, Orders, OrderItems, Products, Shipping, Payments.
Each table has the columns listed in the examples.
customer_id is the primary key for Customers.
order_id is the primary key for Orders.
order_item_id is the primary key for OrderItems.
product_id is the primary key for Products.
shipping_id is the primary key for Shipping.
payment_id is the primary key for Payments.
Orders.customer_id is a foreign key referencing Customers.customer_id.
OrderItems.order_id is a foreign key referencing Orders.order_id.
OrderItems.product_id is a foreign key referencing Products.product_id.
Shipping.order_id is a foreign key referencing Orders.order_id.
Payments.order_id is a foreign key referencing Orders.order_id.
The maximum number of rows across all tables will not exceed 1 million.
The query should be efficient and execute within 5 seconds on a typical RDBMS.

Notes

Pay close attention to the join conditions between tables.
Ensure you are using the correct join types (e.g., INNER JOIN, LEFT JOIN). Since we are filtering for paid orders, INNER JOINs will be appropriate for most relationships.
Consider how to construct the customer's full name from first_name and last_name.
The product_price in the output should be the price of the product as it was when the order was placed (i.e., from the Products table).
This challenge focuses on understanding how to link multiple tables together. Remember to alias your tables for readability, especially in complex queries.