Maximize Prime Item Inventory [Amazon SQL Interview Question]

Effective April 3rd 2024, we have updated the problem statement to provide additional clarity.

Amazon wants to maximize the storage capacity of its 500,000 square-foot warehouse by prioritizing a specific batch of prime items. The specific prime product batch detailed in the table must be maintained.

So, if the prime product batch specified in the column included 1 laptop and 1 side table, that would be the base batch. We could not add another laptop without also adding a side table; they come all together as a batch set.

After prioritizing the maximum number of prime batches, any remaining square footage will be utilized to stock non-prime batches, which also come in batch sets and cannot be separated into individual items.

Write a query to find the maximum number of prime and non-prime batches that can be stored in the 500,000 square feet warehouse based on the following criteria:

• Prioritize stocking prime batches
• After accommodating prime items, allocate any remaining space to non-prime batches

Output the with first followed by , along with the maximum number of batches that can be stocked.

Assumptions:

• Again, products must be stocked in batches, so we want to find the largest available quantity of prime batches, and then the largest available quantity of non-prime batches
• Non-prime items must always be available in stock to meet customer demand, so the non-prime item count should never be zero.
• Item count should be whole numbers (integers).

table:

Example Input:

Example Output:

The dataset you are querying against may have different input & output - this is just an example!

Practice Other Amazon SQL Interview Questions:

To get more insight into the Amazon SQL interview process, practice these Amazon SQL interview questions:

Goal: Find the maximum number of prime and non-prime batches that can be stocked in a 500,000 sq ft warehouse, with prime items being prioritized.

Step 1: Calculate the total square footage and the number of items for both prime and non-prime batches

To start, we summarize the relevant information by calculating the total square footage and the number of items for each (prime and non-prime).

This query returns the total square footage and item count for prime and non-prime items.

Step 2: Calculate the maximum number of prime items

Next, we calculate the maximum number of prime batches based on the total square footage of a single batch of prime items. Here's a formula that explains how many times it can be accommodated in the warehouse:

Maximum Number of Prime Items Batches = FLOOR(Warehouse Area / Total Prime Area)

Maximum Number of Prime Items = FLOOR((Warehouse Area / Total Prime Area) x Number of Prime Batches)

Need a refresher on math function? Check it out in our SQL tutorial here!

The result shows that the warehouse can stock 900 batches of prime items. Each batch of prime items occupies an area of 555.20 sq ft. Therefore, the total area occupied by prime items is 900 * 555.20 sq ft = 499,680 sq ft.

To calculate the remaining warehouse space available for non-prime items, we subtract the total area occupied by prime items from the warehouse's total area: 500,000 sq ft - 499,680 sq ft = 320 sq ft.

Step 3: Calculate the maximum number of non-prime items that can be stored

Now, let's calculate the maximum number of non-prime batches that can be stored in the warehouse.

(1) Allocating Remaining Warehouse Space:

We start by calculating the remaining warehouse space after storing the maximum number of prime batches.

Remaining Warehouse Space = Total Warehouse Area - Total Area Occupied by Prime Items

(2) Calculate the Maximum Number of Non-Prime Items:

With the remaining warehouse space determined, we calculate the maximum number of non-prime batches that can be stored.

Maximum Number of Non-Prime batches = FLOOR((Remaining Warehouse Space / Area per Non-Prime batch) * Number of Non-Prime batches per Area)

Based on the findings:

• Remaining warehouse space = 320 sq ft.
• Area per non-prime batch= 128.50 sq ft.
• Number of non-prime batch per sq ft = 4 items

Maximum Number of Non-Prime Batches = FLOOR((320 sq ft / 128.50 sq ft) x 4 items) = 8 non-prime batches

Here is the final query to accomplish this:

Final results:

Method #2: Using and operator