Back to questions
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:
Column Name | Type |
---|---|
item_id | integer |
item_type | string |
item_category | string |
square_footage | decimal |
item_id | item_type | item_category | square_footage |
---|---|---|---|
1374 | prime_eligible | mini refrigerator | 68.00 |
4245 | not_prime | standing lamp | 26.40 |
2452 | prime_eligible | television | 85.00 |
3255 | not_prime | side table | 22.60 |
1672 | prime_eligible | laptop | 8.50 |
item_type | item_count |
---|---|
prime_eligible | 9285 |
not_prime | 6 |
The dataset you are querying against may have different input & output - this is just an example!
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 items that can be stocked in a 500,000 sq ft warehouse, with prime items being prioritized.
To start, we use the following query to summarize the relevant information:
This query will return the total square footage and the item count for each item type (prime and non-prime).
item_type | total_sqft | item_count |
---|---|---|
prime_eligible | 555.20 | 6 |
not_prime | 128.50 | 4 |
Next, we need to figure out the maximum number of prime items that can be stored in the warehouse by dividing the warehouse's area by the total area of prime items and rounding down to the nearest integer.
item_type | total_sqft | prime_item_combination_count | prime_item_count |
---|---|---|---|
prime_eligible | 555.20 | 900 | 5400 |
The result shows that the warehouse can stock 900 times of prime items which can be mathematically expressed as:
900 times x 555.20 sq ft = 499,680 sq ft area occupied by prime items
The remaining warehouse space to stock non-prime items is 500,000 sq ft - 499,680 sq ft = 320 sq ft.
Finally, we need to calculate the maximum number of non-prime items that can be stored in the warehouse.
(1) Calculate the maximum number of prime items
We've calculated this in the previous step and the maximum number of prime items is 5,400 prime items.
(2) Calculate the maximum number of non-prime items
Storage area - (total area occupied by prime items) / area occupied by non-prime items * number of non-prime items per area
= FLOOR(500,000 sq ft - (900 x 555.20 prime sq ft)) / 128.50 non-prime sq ft* 4 items = 8 non-prime items
Output the results with prime items followed by non-prime items.
Here is the final query to accomplish this:
Final results:
item_type | item_count |
---|---|
prime_eligible | 5400 |
not_prime | 8 |
Method #2: Using and operator
PostgreSQL 14