Objective

Write a SQL query to determine the maximum number of prime and non-prime batches that can be stored in a 500,000 square-foot warehouse. Prime batches must be fully stocked first, and any remaining space should be used for non-prime batches.

Table Schema:

• inventory

Task Requirements:

• Calculate the total number of prime batches and their total square footage
• All prime batches are stocked first (assume they fit within the warehouse)
• Use the remaining space to calculate the maximum number of non-prime batches that can be stored
• Output prime results before non-prime results
  
  

Output columns: item_type, max_batch_count

WITH summary AS (
    SELECT
        item_type,
        SUM(item_count) AS batch_count,
        SUM(square_footage * item_count) AS total_sqft
    FROM inventory
    GROUP BY item_type
)
SELECT
    item_type,
    CASE
        WHEN item_type = 'prime_eligible' THEN batch_count
        ELSE FLOOR(
            (500000 - (SELECT total_sqft FROM summary WHERE item_type = 'prime_eligible'))
            * batch_count::DECIMAL
            / total_sqft
        )
    END AS max_batch_count
FROM summary
ORDER BY item_type DESC;

Explanation

Step 1: Aggregate batch data per item type

WITH summary AS (
    SELECT
        item_type,
        SUM(item_count) AS batch_count,
        SUM(square_footage * item_count) AS total_sqft
    FROM inventory
    GROUP BY item_type
)

The CTE groups inventory by item_type and calculates two values for each type: the total number of batches (SUM(item_count)) and the total square footage required (SUM(square_footage * item_count)).

Step 2: Stock all prime batches first

WHEN item_type = 'prime_eligible' THEN batch_count

Since prime batches have priority, the full batch_count is returned for prime_eligible items. All prime inventory gets stocked in the warehouse.

Step 3: Calculate remaining space for non-prime batches

ELSE FLOOR(
    (500000 - (SELECT total_sqft FROM summary WHERE item_type = 'prime_eligible'))
    * batch_count::DECIMAL
    / total_sqft
)

For non-prime items, the subquery (SELECT total_sqft FROM summary WHERE item_type = 'prime_eligible') retrieves the space consumed by prime batches. Subtracting from 500000 gives the remaining warehouse capacity. Multiplying by batch_count::DECIMAL / total_sqft converts the remaining space into the equivalent number of non-prime batches. The ::DECIMAL cast ensures decimal division, and FLOOR() rounds down to a whole number of batches.

Step 4: Order the output

ORDER BY item_type DESC

Sorting by item_type DESC places prime_eligible before not_prime alphabetically, matching the priority order.