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:

Example Input:

Example Output:

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 ().

Goal: Find the maximum number of combination of the prime and non-prime items to be stocked, with prime items prioritized first.

Greedy Approach: Maximize the number of prime items first, then fill the remaining warehouse space with the maximum number of non-prime items. The output should be grouped by item type ('prime_eligible' and 'not_prime').

First, we build a summary table of the necessary fields: item type ('prime_eligible', 'not_prime'), then sum the square footage and count of items.

Results from the above query:

refers to the total square feet for the specified . The 4 'not_prime' items has a total square feet of 128.50.

After converting the above query into a CTE called , we find the number of times that we can stock the prime items in the warehouse space, since those have the highest priority.
We accomplish this by dividing 500,000 (the square-footage of the space) by the total square footage of prime items, and truncating the result to 0 decimal places using .

We can stock 900 times of prime eligible items which can be mathematically expressed as:

900 times x 555.20 sq ft = 499,680 sq ft

Hence, the remaining warehouse space is 500,000 sq ft - 499,680 sq ft = 320 sq ft.

Next, we convert the previous query into a second CTE called , and write a similar query to find the number of times that we can stock the non-prime items to fill the remaining 320 sq ft.

Note that we use the sub-select here to reference the square footage already taken up (~499,80 sq ft) by prime-eligible items.

We can stock 8 total non-prime items; in other words, we can stock the established combination of 4 non-prime items (see first results table) twice. This is expressed in the query as:

It can be mathematically expressed in two steps as:
Step 1: (500,000 sq ft-(900 times * 555.20 prime sq ft)) / 128.50 non-prime sq ft =
~2 sq ft per non-prime item
Step 2: ~2 sq ft per non-prime item * 4 items per non-prime group = 8 non-prime items total

Now that we have the number of prime and non-prime items in two separate tables, we can combine them using the operator. Note that only works when the two SELECT statements have the same number of result fields with similar data types.

Final results:

Solution: