logo

Back to questions

Maximize Prime Item Inventory

Hard

Amazon wants to maximize the number of items it can stock in a 500,000 square feet warehouse. It wants to stock as many prime items as possible, and afterwards use the remaining square footage to stock the most number of non-prime items.

Write a SQL query to find the number of prime and non-prime items that can be stored in the 500,000 square feet warehouse. Output the item type and number of items to be stocked.

table:

Column NameType
item_idinteger
item_typestring
item_categorystring
square_footagedecimal

Example Input:

item_iditem_typeitem_categorysquare_footage
1374prime_eligiblemini refrigerator68.00
4245not_primestanding lamp26.40
2452prime_eligibletelevision85.00
3255not_primeside table22.60
1672prime_eligiblelaptop8.50

Example Output:

item_typeitem_count
prime_eligible9285
not_prime6

To prioritise storage of prime_eligible items:

The combination of the prime_eligible items has a total square footage of 161.50 sq ft ().

To prioritise the storage of the prime_eligible items, we find the number of times that we can stock the combination of the prime_eligible items which are 3,095 times, mathematically expressed as:

Then, we multiply 3,095 times with 3 items (because we're asked to output the number of items to stock), which gives us 9,285 items.

Stocking not_prime items with remaining storage space:

After stocking the prime_eligible items, we have a remaining 157.50 sq ft ().

Then, we divide by the total square footage for the combination of 2 not_prime items which is mathematically expressed as so the total number of not_prime items that we can stock is 6 items ().

PostgreSQL 14

Refer friends to get bonus content & cool prizes.