Warehouse Manager - Practice Coding Problems

Warehouse Manager - Problem

Database Easy

Warehouse Inventory Volume Calculator

You're managing a sophisticated warehouse system that needs to calculate the total volume occupied by inventory in each warehouse.

Given two tables:

Warehouse Table: Contains information about which products are stored in each warehouse and their quantities.

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| name         | varchar |
| product_id   | int     |
| units        | int     |
+--------------+---------+

Products Table: Contains the dimensions of each product in feet.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| Width         | int     |
| Length        | int     |
| Height        | int     |
+---------------+---------+

Goal: Calculate the total cubic feet of volume that the inventory occupies in each warehouse. For each product, multiply its dimensions (Width × Length × Height) by the number of units stored to get the total volume contribution.

Input & Output

example_1.sql — Basic Warehouse Calculation

$ Input: Warehouse: | name | product_id | units | |------|------------|-------| | LCHouse1 | 1 | 1 | | LCHouse1 | 2 | 10 | | LCHouse1 | 3 | 5 | | LCHouse2 | 1 | 2 | | LCHouse2 | 2 | 2 | | LCHouse3 | 4 | 1 | Products: | 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-Book | 4 | 100 | 10 |

› Output: | warehouse_name | volume | |----------------|--------| | LCHouse1 | 12250 | | LCHouse2 | 10250 | | LCHouse3 | 4000 |

💡 Note: LCHouse1: TV(1×5×50×40) + KeyChain(10×5×5×5) + Phone(5×2×10×10) = 10000 + 1250 + 1000 = 12250. LCHouse2: TV(2×5×50×40) + KeyChain(2×5×5×5) = 20000 + 250 = 10250. LCHouse3: Book(1×4×100×10) = 4000.

example_2.sql — Single Product Multiple Warehouses

$ Input: Warehouse: | name | product_id | units | |------|------------|-------| | East | 1 | 5 | | West | 1 | 3 | | North | 1 | 2 | Products: | product_id | product_name | Width | Length | Height | |------------|--------------|-------|--------|--------| | 1 | Box | 2 | 2 | 2 |

› Output: | warehouse_name | volume | |----------------|--------| | East | 40 | | West | 24 | | North | 16 |

💡 Note: Each box has volume 2×2×2 = 8 cubic feet. East: 5×8 = 40, West: 3×8 = 24, North: 2×8 = 16.

example_3.sql — Empty Warehouse Edge Case

$ Input: Warehouse: | name | product_id | units | |------|------------|-------| | Main | 1 | 0 | | Secondary | 2 | 1 | Products: | product_id | product_name | Width | Length | Height | |------------|--------------|-------|--------|--------| | 1 | Large | 10 | 10 | 10 | | 2 | Small | 1 | 1 | 1 |

› Output: | warehouse_name | volume | |----------------|--------| | Main | 0 | | Secondary | 1 |

💡 Note: Main warehouse has 0 units of the large product, so volume is 0×10×10×10 = 0. Secondary has 1 unit of small product: 1×1×1×1 = 1.

Constraints

1 ≤ number of warehouses ≤ 100
1 ≤ number of products ≤ 1000
0 ≤ units ≤ 10⁵
1 ≤ Width, Length, Height ≤ 1000
All product_ids in Warehouse table exist in Products table

Visualization

Tap to expand

Understanding the Visualization

Match Products to Inventory

Connect each warehouse item with its product dimensions using product_id

Calculate Individual Volumes

For each item: multiply Width × Length × Height × Quantity

Sum by Warehouse

Group all products by warehouse and add up their total volumes

Key Takeaway

🎯 Key Insight: SQL JOINs efficiently combine related data from multiple tables, allowing us to calculate complex derived values like volume in a single optimized query rather than multiple lookups.

Asked in

a Amazon 45 W Walmart 32 F FedEx 28 U UPS 24

The optimal solution uses an INNER JOIN to combine the Warehouse and Products tables on product_id, then calculates volume as Width × Length × Height × units for each product, and finally groups by warehouse name to sum the total volumes. This approach is efficient with O(n + m) time complexity and leverages database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ INNER JOIN (Optimal)	O(n + m)	O(k)	Use INNER JOIN to combine tables efficiently in a single query
Multiple Subqueries (Inefficient)	O(n × m)	O(1)	Use correlated subqueries to look up dimensions for each warehouse item

INNER JOIN (Optimal) — Algorithm Steps

JOIN Warehouse and Products tables on product_id
Calculate volume for each row: Width × Length × Height × units
GROUP BY warehouse name and SUM the volumes

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Match warehouse and product records by product_id

Calculate Volume

For each matched row: Width × Length × Height × units

Group and Sum

Group by warehouse name and sum all volumes

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of both tables with hash join optimization

✓ Linear Growth

Space Complexity

O(k)

Space for result set where k is number of warehouses

✓ Linear Space

28.4K Views

Medium Frequency

~15 min Avg. Time

892 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

INNER JOIN (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler