Order Fulfillment Eligibility and Discount Calculation

This challenge requires you to implement complex business logic within SQL to determine order fulfillment eligibility and calculate applicable discounts. Many e-commerce platforms need to dynamically assess whether an order can be fulfilled based on inventory, customer status, and promotional rules, and then apply appropriate discounts. This problem simulates that process.

Problem Description

You are tasked with writing SQL queries to determine if an order is eligible for fulfillment and, if so, calculate the final price after applying any applicable discounts. The logic involves checking inventory levels, customer loyalty tiers, and promotional codes. The database schema is described below.

Database Schema:

• Customers: (CustomerID, Name, LoyaltyTier) - LoyaltyTier is an ENUM ('Bronze', 'Silver', 'Gold', 'Platinum')
• Products: (ProductID, Name, Price, Inventory)
• Orders: (OrderID, CustomerID, OrderDate)
• OrderItems: (OrderID, ProductID, Quantity)
• Promotions: (PromotionID, Code, DiscountPercentage, StartDate, EndDate, LoyaltyTierRequirement) - LoyaltyTierRequirement can be NULL (meaning no loyalty tier restriction) or one of the LoyaltyTier values from the Customers table.

What needs to be achieved:

1. Fulfillment Eligibility: An order is eligible for fulfillment if all items in the order have sufficient inventory.
2. Discount Calculation:
   • Loyalty Discount: Customers with 'Silver', 'Gold', or 'Platinum' loyalty tiers receive a 5% discount on the total order price.
   • Promotion Code Discount: If a valid promotion code is applied (i.e., the code exists in the Promotions table, is within its start and end dates, and the customer's loyalty tier meets the requirement), the discount percentage specified in the Promotions table is applied. A customer can only have one promotion code applied. If multiple codes are valid, choose the one with the highest discount percentage.
3. Final Price: Calculate the final price after applying any applicable discounts.

Key Requirements:

• The solution must be implemented using SQL queries.
• The queries should be efficient and handle potential edge cases gracefully.
• The solution should return the OrderID, FulfillmentStatus (Eligible/Ineligible), and FinalPrice.

Expected Behavior:

• If an order is not eligible for fulfillment, the FulfillmentStatus should be 'Ineligible', and the FinalPrice should be NULL.
• If an order is eligible for fulfillment, the FulfillmentStatus should be 'Eligible', and the FinalPrice should be calculated correctly, considering both loyalty and promotion discounts (if applicable).
• If no promotion code is applicable, only the loyalty discount (if applicable) should be applied.
• If both a loyalty discount and a promotion code are applicable, the promotion code discount should take precedence.

Edge Cases to Consider:

• Orders with no items.
• Products with zero inventory.
• Promotion codes that have expired.
• Promotion codes with loyalty tier requirements that don't match the customer's tier.
• Multiple valid promotion codes – choose the one with the highest discount.
• Orders placed on the exact start or end date of a promotion.

Examples

Example 1:

Input:
Customers: (1, 'Alice', 'Silver'), (2, 'Bob', 'Bronze')
Products: (101, 'Laptop', 1200, 5), (102, 'Mouse', 25, 10)
Orders: (1, 1, '2024-01-15'), (2, 2, '2024-01-16')
OrderItems: (1, 101, 1), (1, 102, 2), (2, 101, 1)
Promotions: (1, 'SUMMER20', 20, '2024-01-01', '2024-01-31', 'Silver')

Output:
OrderID | FulfillmentStatus | FinalPrice
------- | ------------------ | ----------
1       | Eligible           | 2280.00  (1200 + 25*2 - 5% - 20% on Laptop only)
2       | Ineligible         | NULL

Explanation: Order 1 is eligible because there's enough inventory. Alice is Silver, so she gets a 5% discount. The SUMMER20 promotion is valid for Silver customers and applies a 20% discount to the Laptop.

Example 2:

Input:
Customers: (1, 'Alice', 'Gold'), (2, 'Bob', 'Bronze')
Products: (101, 'Laptop', 1200, 2), (102, 'Mouse', 25, 5)
Orders: (1, 1, '2024-01-15'), (2, 2, '2024-01-16')
OrderItems: (1, 101, 1), (1, 102, 2), (2, 101, 1)
Promotions: (1, 'WINTER10', 10, '2023-12-20', '2024-01-20', NULL)

Output:
OrderID | FulfillmentStatus | FinalPrice
------- | ------------------ | ----------
1       | Ineligible         | NULL
2       | Ineligible         | NULL

Explanation: Order 1 is ineligible because there's only 2 laptops in inventory, and the order requires 1. Order 2 is also ineligible.

Example 3: (Edge Case - Multiple Promotions)

Input:
Customers: (1, 'Alice', 'Silver')
Products: (101, 'Laptop', 1200, 5)
Orders: (1, 1, '2024-01-15')
OrderItems: (1, 101, 1)
Promotions: (1, 'SUMMER20', 20, '2024-01-01', '2024-01-31', 'Silver'), (2, 'SPRING15', 15, '2024-01-01', '2024-01-31', 'Silver')

Output:
OrderID | FulfillmentStatus | FinalPrice
------- | ------------------ | ----------
1       | Eligible           | 960.00 (1200 - 20%)

Explanation: Both SUMMER20 and SPRING15 are valid for Alice. SUMMER20 has a higher discount (20% vs 15%), so it's applied.

Constraints

• All dates are in YYYY-MM-DD format.
• Discount percentages are represented as integers (e.g., 20 for 20%).
• Inventory levels will always be non-negative integers.
• The database will contain at least one customer, one product, and one order.
• The solution should be optimized for reasonable performance on datasets with up to 10,000 orders.

Notes

• Consider using subqueries or Common Table Expressions (CTEs) to break down the logic into smaller, more manageable parts.
• Pay close attention to the order of operations when calculating the final price (loyalty discount vs. promotion discount).
• Think about how to handle cases where a promotion code is valid but the customer doesn't meet the loyalty tier requirement.
• The problem focuses on the SQL logic; you don't need to worry about database setup or data insertion. Assume the data exists and is valid.
• The FinalPrice should be rounded to two decimal places.