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: Warehouse OptimizationInventory Tableiditem_typecategorysqft1prime_eligiblebooks10002prime_eligibleelectronics10003not_primesports2000CTE Processing1. Calculate Averages2. Prime First Priority3. Remaining SpaceResultitem_typeitem_countprime_eligible500not_prime0Warehouse Space Allocation (500,000 sqft total)Prime Items: 500 × 1000 = 500,000 sqftRemaining: 0 sqft (No space for not_prime)Prime items completely fill the warehouse
Understanding the Visualization
1
Input
Inventory table with item types and space requirements
2
CTE Calculation
Calculate averages and allocate space by priority
3
Output
Maximum item counts for each type
Key Takeaway
🎯 Key Insight: Use CTEs for complex multi-step calculations with priority allocation in SQL optimization problems

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