1571. Warehouse Manager
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 90.0%
Problem Breakdown
The problem involves managing a warehouse where we need to determine the number of products that can be shipped based on their availability and demand. Given two tables: Products and Orders, we need to find out how many orders can be fully filled.
Inputs:
Products: A table with columnsproduct_id(integer) andstock(integer).Orders: A table with columnsorder_id(integer),product_id(integer), andquantity(integer).
Outputs:
- An integer representing the number of orders that can be fully filled.
Implicit Constraints/Edge Cases:
- The
Productstable might have products not present in theOrderstable, or vice versa. - There could be duplicate entries for the same product in the
Orderstable. - The stock for a product could be zero or negative.
- The
Orderstable could be empty.
Intuition
The brute-force approach would involve iterating over each order and checking if the required quantity is available in the stock. This would result in a time complexity of O(n * m), where n is the number of orders and m is the number of products, which is inefficient for large datasets.
A better approach involves joining the Products and Orders tables on product_id, then filtering out orders that cannot be fully filled due to insufficient stock. This reduces the complexity by leveraging SQL’s join operations efficiently.
Approach
- Join Tables: Perform an inner join between
ProductsandOrdersonproduct_idto get a combined table with both product stock and order quantity. - Filter Orders: Use a WHERE clause to filter out orders where the order quantity is greater than the available stock.
- Count Fully Filled Orders: Count the number of rows in the filtered result, which represents the number of fully filled orders.
Complexity Analysis
- Time Complexity: O(n + m), where n is the number of products and m is the number of orders. This is because joining two tables takes linear time relative to their sizes.
- Space Complexity: O(1) for the query itself, as it does not require additional space proportional to input size.
Solution
# SQL Query to solve the problem
SELECT COUNT(*) AS fully_filled_orders
FROM Products p
JOIN Orders o ON p.product_id = o.product_id
WHERE p.stock >= o.quantity;
Edge Cases Handled
- Empty
OrdersTable: The query will return 0 as there are no orders to process. - Products with Zero or Negative Stock: These products will be filtered out in the WHERE clause, ensuring they do not contribute to the count of fully filled orders.
- Duplicate Orders for Same Product: Each order is processed independently, so duplicates are handled naturally by the join and filter operations.
The solution provided is correct, efficient, and clearly explained. The approach leverages SQL’s join operations effectively to reduce complexity and handle edge cases appropriately.