Maximize Items - Problem

You are given a table Inventory with the following structure:

  • item_id (int): Unique identifier for each item
  • item_type (varchar): Type of item ('prime_eligible' or 'not_prime')
  • item_category (varchar): Category of the item
  • square_footage (decimal): Square footage required for the item

Leetcode warehouse has 500,000 square feet of storage space and wants to maximize the number of items it can stock. The strategy is:

  1. First, stock as many prime_eligible items as possible
  2. Then, use the remaining space to stock the maximum number of not_prime items

Write a SQL query to find the number of prime and non-prime items that can be stored. Output the item_type with prime_eligible followed by not_prime and the maximum number of items that can be stocked.

Note: Item count must be a whole number (integer). If the count for the not_prime category is 0, output 0. Return results ordered by item count in descending order.

Table Schema

Inventory
Column Name Type Description
item_id PK int Unique identifier for each item
item_type varchar Type of item ('prime_eligible' or 'not_prime')
item_category varchar Category of the item
square_footage decimal Square footage required for the item
Primary Key: item_id
Note: Each row represents one item with its storage requirements

Input & Output

Example 1 — Basic Optimization
Input Table:
item_id item_type item_category square_footage
1 prime_eligible books 1000
2 prime_eligible electronics 1000
3 not_prime sports 2000
4 not_prime home 3000
Output:
item_type item_count
prime_eligible 500
not_prime 0
💡 Note:

Prime items average 1000 sqft each, so we can fit 500,000/1000 = 500 prime items. This uses all available space (500 × 1000 = 500,000), leaving 0 space for non-prime items.
Example 2 — Mixed Allocation
Input Table:
item_id item_type item_category square_footage
1 prime_eligible books 2000
2 prime_eligible electronics 2000
3 not_prime sports 1000
4 not_prime home 1000
Output:
item_type item_count
not_prime 250
prime_eligible 2
💡 Note:

Prime items average 2000 sqft each, so we can fit 2 prime items (limited by available count). This uses 4000 sqft, leaving 496,000 sqft. Non-prime items average 1000 sqft each, so we can fit 496 non-prime items, but limited to available count of 2, so result is ordered by count.
Example 3 — No Prime Items
Input Table:
item_id item_type item_category square_footage
1 not_prime sports 1500
2 not_prime home 2500
Output:
item_type item_count
not_prime 2
prime_eligible 0
💡 Note:

No prime items available, so all 500,000 sqft goes to non-prime items. Non-prime items average 2000 sqft each, allowing 250 items, but only 2 items are available. Prime eligible shows 0 count.

Constraints

  • 1 ≤ item_id ≤ 10^6
  • item_type is either 'prime_eligible' or 'not_prime'
  • 1.00 ≤ square_footage ≤ 100000.00
  • Total warehouse space is 500,000 square feet

Visualization

Tap to expand
Maximize Items in Warehouse INPUT Inventory Table item_id type category sq_ft 1 prime elec 500 2 not_prime furn 1200 3 prime toys 800 ... more rows ... Warehouse Capacity 500,000 sq ft prime_eligible not_prime GOAL 1. Maximize PRIME items 2. Fill remaining with NOT_PRIME items ALGORITHM STEPS 1 Calculate Prime Space SUM(sq_ft) WHERE type='prime' total_prime_sqft = X 2 Count Prime Items COUNT(*) WHERE type='prime' prime_count = N items 3 Calc Remaining Space remaining = 500000 - prime_sqft remaining_space = Y 4 Fill with Not_Prime Add items until space full not_prime_count = M items WITH prime_sum AS (...), not_prime_calc AS (...) SELECT ... UNION ALL ... FINAL RESULT WAREHOUSE (500,000 sq ft) PRIME ITEMS Maximized First NOT_PRIME ITEMS Remaining Space OUTPUT item_type item_count prime_eligible N not_prime M STATUS: OK Prime items maximized Remaining space filled Key Insight: This is a two-phase greedy optimization problem. First, calculate total space needed for ALL prime items (they have priority). Then, use running sum / window function to fit as many not_prime items as possible in the remaining space. Use UNION ALL to combine both result sets ordered by item_type. TutorialsPoint - Maximize Items | Optimal Solution

Related Problems

Asked in
Amazon 12 Walmart 8 Target 5
23.4K Views
Medium Frequency
~25 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen