Warehouse Manager - Problem

You are given two tables: Warehouse and Products.

The Warehouse table contains information about products stored in each warehouse, including the warehouse name, product ID, and number of units.

The Products table contains product dimensions (Width, Length, and Height) in feet for each product.

Write a SQL query to calculate the total volume in cubic feet that the inventory occupies in each warehouse. The volume of each product is calculated as Width × Length × Height, and the total volume per warehouse is the sum of (volume × units) for all products in that warehouse.

Return the result table in any order.

Table Schema

Warehouse
Column Name Type Description
name PK varchar Warehouse name
product_id PK int Product identifier
units int Number of units stored
Primary Key: (name, product_id)
Products
Column Name Type Description
product_id PK int Product identifier
product_name varchar Product name
Width int Product width in feet
Length int Product length in feet
Height int Product height in feet
Primary Key: product_id

Input & Output

Example 1 — Basic Warehouse Volume Calculation
Input Tables:
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-T-Shirt 4 10 20
Output:
warehouse_name volume
LCHouse1 12250
LCHouse2 20250
LCHouse3 800
💡 Note:

For each warehouse, we calculate the total volume by multiplying each product's dimensions (Width × Length × Height) by the number of units, then summing all products in that warehouse.

LCHouse1: (1×5×50×40) + (10×5×5×5) + (5×2×10×10) = 10000 + 1250 + 1000 = 12250

LCHouse2: (2×5×50×40) + (2×5×5×5) = 20000 + 250 = 20250

LCHouse3: (1×4×10×20) = 800
Example 2 — Empty Warehouse
Input Tables:
Warehouse
name product_id units
Warehouse1 1 3
Products
product_id product_name Width Length Height
1 Product1 2 3 4
Output:
warehouse_name volume
Warehouse1 72
💡 Note:

Simple case with one warehouse and one product. Volume = 3 units × (2×3×4) = 3 × 24 = 72 cubic feet.

Constraints

  • 1 ≤ units ≤ 1000
  • 1 ≤ Width, Length, Height ≤ 100
  • product_id is unique in Products table
  • (name, product_id) is unique in Warehouse table

Visualization

Tap to expand
Warehouse Volume Calculator INPUT DATA warehouses id name 1 North 2 South products wh_id L W H units 1 2 3 4 10 1 5 5 5 5 2 3 3 3 20 WH 1 North WH 2 South P1 P2 P3 Products distributed across warehouses ALGORITHM STEPS 1 JOIN Tables Match products.wh_id with warehouses.id 2 Calculate Volume vol = L x W x H x units 3 GROUP BY warehouse Aggregate products per warehouse 4 SUM Total Volume Sum all product volumes for each warehouse Calculation Example: WH1: (2x3x4x10)+(5x5x5x5) = 240 + 625 = 865 ft³ WH2: (3x3x3x20) = 540 ft³ FINAL RESULT Total Volume by Warehouse Warehouse Volume (ft³) North 865 South 540 NORTH 865 cubic ft SOUTH 540 GRAND TOTAL: 1,405 cubic feet OK - DONE Key Insight: The optimal solution uses SQL JOIN to combine warehouse and product data, then GROUP BY with SUM aggregation. Volume calculation (L x W x H x units) happens in a single pass. Time complexity: O(n) where n = number of products. This avoids nested loops by leveraging database indexing on warehouse_id for efficient joins. TutorialsPoint - Warehouse Manager | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
23.4K Views
Medium Frequency
~8 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