Warehouse Manager - Solution & Explanation
Problem Statement
Table: Warehouse
+--------------+---------+ | Column Name | Type | +--------------+---------+ | name | varchar | | product_id | int | | units | int | +--------------+---------+ (name, product_id) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the information of the products in each warehouse.
Table: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | Width | int | | Length | int | | Height | int | +---------------+---------+ product_id is the primary key (column with unique values) for this table. Each row of this table contains information about the product dimensions (Width, Lenght, and Height) in feets of each product.
Write a solution to report the number of cubic feet of volume the inventory occupies in each warehouse.
Return the result table in any order.
The query result format is in the following example.
Example 1:
Input:
Warehouse table:
+------------+--------------+-------------+
| name | product_id | units |
+------------+--------------+-------------+
| LCHouse1 | 1 | 1 |
| LCHouse1 | 2 | 10 |
| LCHouse1 | 3 | 5 |
| LCHouse2 | 1 | 2 |
| LCHouse2 | 2 | 2 |
| LCHouse3 | 4 | 1 |
+------------+--------------+-------------+
Products table:
+------------+--------------+------------+----------+-----------+
| product_id | product_name | Width | Length | Height |
+------------+--------------+------------+----------+-----------+
| 1 | LC-TV | 5 | 50 | 40 |
| 2 | LC-KeyChain | 5 | 5 | 5 |
| 3 | LC-Phone | 2 | 10 | 10 |
| 4 | LC-T-Shirt | 4 | 10 | 20 |
+------------+--------------+------------+----------+-----------+
Output:
+----------------+------------+
| warehouse_name | volume |
+----------------+------------+
| LCHouse1 | 12250 |
| LCHouse2 | 20250 |
| LCHouse3 | 800 |
+----------------+------------+
Explanation:
Volume of product_id = 1 (LC-TV), 5x50x40 = 10000
Volume of product_id = 2 (LC-KeyChain), 5x5x5 = 125
Volume of product_id = 3 (LC-Phone), 2x10x10 = 200
Volume of product_id = 4 (LC-T-Shirt), 4x10x20 = 800
LCHouse1: 1 unit of LC-TV + 10 units of LC-KeyChain + 5 units of LC-Phone.
Total volume: 1*10000 + 10*125 + 5*200 = 12250 cubic feet
LCHouse2: 2 units of LC-TV + 2 units of LC-KeyChain.
Total volume: 2*10000 + 2*125 = 20250 cubic feet
LCHouse3: 1 unit of LC-T-Shirt.
Total volume: 1*800 = 800 cubic feet.
Approach Overview
Problem Overview: Each warehouse stores multiple products with a certain number of units. Every product has dimensions in the Products table. The task is to compute the total storage volume used by each warehouse, where volume for one product equals width × length × height × units.
Approach 1: Inner Join + Group By + Sum Function (O(n) time, O(1) extra space)
The solution joins the Warehouse table with the Products table using product_id. After the join, each row contains both the number of units stored and the product’s dimensions. Compute the volume contribution per row using width * length * height * units, then aggregate the result with SUM() for each warehouse.
The key idea is that product dimensions live in a separate table, so you must combine them using an INNER JOIN. Once the data is combined, standard SQL aggregation handles the total volume calculation. Use GROUP BY warehouse_name so each warehouse appears once in the result with its summed volume.
This approach scans the joined dataset once and performs aggregation per group. In most database engines, the join and aggregation operate in linear time relative to the number of rows, giving roughly O(n) processing time and O(1) additional space outside the result set.
Approach 2: Join with Precomputed Product Volume (O(n) time, O(1) extra space)
Another clean pattern computes product volume first and then joins that derived result with the warehouse table. Create a subquery that selects product_id and calculates width * length * height as product volume. Then join this derived table with Warehouse and multiply the volume by units before aggregating with SUM().
This separates dimension calculation from warehouse aggregation, which can make the query easier to read in complex schemas. Performance is similar because the database optimizer typically inlines the derived table. Complexity remains about O(n) time with constant extra memory.
Both solutions rely on core relational operations such as database queries, SQL aggregation, and table joins. These patterns appear frequently in analytics-style interview questions.
Recommended for interviews: The INNER JOIN + GROUP BY + SUM approach is the expected solution. It shows you understand relational joins and aggregation. A derived-table version demonstrates query structuring skills, but the core concept interviewers look for is joining tables and computing aggregated metrics correctly.
Solution
We can use an inner join to join the Warehouse table and the Products table on the condition of product_id, and then group by warehouse name to calculate the inventory of each warehouse using the SUM function.
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Inner Join + GROUP BY + SUM | O(n) | O(1) | Standard SQL aggregation when combining multiple tables and computing totals per group |
| Join with Precomputed Product Volume (Subquery) | O(n) | O(1) | When separating calculation logic from aggregation improves readability |
Video Solution
LeetCode 1571 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 5,424 views views
Watch 1 more video solutions →Frequently Asked Questions
Is Warehouse Manager easy or hard?
Warehouse Manager Python/Java solution
How to solve Warehouse Manager in O(n)?
What is the best approach for Warehouse Manager?
Is Warehouse Manager asked at Google/Amazon/Meta?
What data structure is used in Warehouse Manager?
What is the time complexity of Warehouse Manager?
Ready to solve this problem?
Practice Warehouse Manager with our built-in code editor and test cases.
Practice on FleetCode