logo

Back to questions

Maximize Prime Item Inventory [Amazon SQL Interview Question]

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 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 with followed by and the maximum number of items that can be stocked.

Effective April 3rd 2023, we added some new assumptions to the question to provide additional clarity.

Assumptions:

  • Prime and non-prime items have to be stored in equal amounts, regardless of their size or square footage. This implies that prime items will be stored separately from non-prime items in their respective containers, but within each container, all items must be in the same amount.
  • 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:

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

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: Amazon SQL Interview Guide

PostgreSQL 14