Table: Inventory
+----------------+---------+ | Column Name | Type | +----------------+---------+ | item_id | int | | item_type | varchar | | item_category | varchar | | square_footage | decimal | +----------------+---------+ item_id is the column of unique values for this table. Each row includes item id, item type, item category and sqaure footage.
Leetcode warehouse 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 solution 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 prime_eligible followed by not_prime and the maximum number of items that can be stocked.
Note:
0, you should output 0 for that particular category.Return the result table ordered by item count in descending order.
The result format is in the following example.
Example 1:
Input: Inventory table: +---------+----------------+---------------+----------------+ | item_id | item_type | item_category | square_footage | +---------+----------------+---------------+----------------+ | 1374 | prime_eligible | Watches | 68.00 | | 4245 | not_prime | Art | 26.40 | | 5743 | prime_eligible | Software | 325.00 | | 8543 | not_prime | Clothing | 64.50 | | 2556 | not_prime | Shoes | 15.00 | | 2452 | prime_eligible | Scientific | 85.00 | | 3255 | not_prime | Furniture | 22.60 | | 1672 | prime_eligible | Beauty | 8.50 | | 4256 | prime_eligible | Furniture | 55.50 | | 6325 | prime_eligible | Food | 13.20 | +---------+----------------+---------------+----------------+ Output: +----------------+-------------+ | item_type | item_count | +----------------+-------------+ | prime_eligible | 5400 | | not_prime | 8 | +----------------+-------------+ Explanation: - The prime-eligible category comprises a total of 6 items, amounting to a combined square footage of 555.20 (68 + 325 + 85 + 8.50 + 55.50 + 13.20). It is possible to store 900 combinations of these 6 items, totaling 5400 items and occupying 499,680 square footage. - In the not_prime category, there are a total of 4 items with a combined square footage of 128.50. After deducting the storage used by prime-eligible items (500,000 - 499,680 = 320), there is room for 2 combinations of non-prime items, accommodating a total of 8 non-prime items within the available 320 square footage. Output table is ordered by item count in descending order.
Problem Overview: The task is to determine how many items can be purchased within a fixed budget using SQL. Items belong to different categories (for example, priority items and regular items), and the goal is to maximize the total number of items purchased while respecting the spending constraint.
Approach 1: Join Query + UNION ALL (O(n) time, O(1) space)
This approach splits the logic into two SQL result sets and combines them using UNION ALL. First, compute how many items from the higher‑priority category can be purchased within the total budget. This is done using SUM() aggregation and arithmetic operations to determine how many full sets of those items fit into the budget.
Next, calculate the remaining budget after purchasing those items. With the leftover budget, compute how many items from the second category can be purchased. A JOIN or subquery helps reuse the aggregated totals and ensures the second calculation depends on the remaining budget from the first step.
The two results are then combined with UNION ALL, producing separate rows for each category. This pattern keeps the query readable and avoids deeply nested conditional logic. SQL engines handle the aggregation in a single scan of the relevant rows, giving an effective time complexity of O(n) where n is the number of items.
This technique is common in complex SQL interview problems where different categories require separate calculations before combining results. UNION ALL is preferred over UNION because duplicate elimination is unnecessary and would add extra overhead.
Recommended for interviews: The JOIN + UNION ALL strategy is the expected approach. It demonstrates strong understanding of database query design, aggregation, and conditional budgeting logic. Interviewers typically look for clean separation of category calculations and efficient use of SQL joins or subqueries rather than procedural logic.
First, we calculate the total area of all items of type prime_eligible and record it in the s field of table T.
Next, we calculate the number of items of type prime_eligible and not_prime respectively. For items of type prime_eligible, the number of portions we can store is \lfloor \frac{500000}{s} \rfloor. For items of type not_prime, the number of portions we can store is \lfloor \frac{500000 \mod s}{sum s1} \rfloor. Where sum s1 is the total area of all items of type not_prime. Multiplying by the number of items of type prime_eligible and not_prime respectively gives us our result.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Join Query + UNION ALL | O(n) | O(1) | Best for SQL interview problems where multiple item categories must be calculated separately and then combined. |
| Single Aggregation with Conditional Logic | O(n) | O(1) | Useful when category rules are simple and can be handled with CASE statements instead of multiple queries. |
| Nested Subquery Budget Simulation | O(n) | O(1) | Works when step-by-step budget deduction is needed but can become harder to read and maintain. |
Leetcode HARD 3052 - Maximize Items FLOOR CASE WHEN SQL - Explained by Everyday Data Science • Everyday Data Science • 815 views views
Practice Maximize Items with our built-in code editor and test cases.
Practice on FleetCode