LeetCode 1571 - Warehouse Manager

This problem asks us to calculate the total storage volume occupied by products inside each warehouse. We are given two database tables: The Warehouse table tells us which products are stored in each warehouse and how many units of each product exist there.

leetcodeeasydatabase

LeetCode Problem 1571

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to calculate the total storage volume occupied by products inside each warehouse.

We are given two database tables:

The Warehouse table tells us which products are stored in each warehouse and how many units of each product exist there.

Warehouse(name, product_id, units)

The Products table stores the physical dimensions of every product.

Products(product_id, product_name, Width, Length, Height)

The volume of a single product is:

Width × Length × Height

Since a warehouse may contain multiple units of the same product, the total contribution of one product inside a warehouse is:

units × product_volume

The goal is to compute the total occupied volume for every warehouse and return:

warehouse_name | volume

The result may be returned in any order.

What the Input Represents

The Warehouse table represents inventory distribution:

name product_id units
Warehouse A 1 5

This means Warehouse A stores 5 units of product 1.

The Products table represents product dimensions:

product_id Width Length Height
1 2 3 4

This means the product volume is:

2 × 3 × 4 = 24

If Warehouse A stores 5 units, the occupied volume becomes:

5 × 24 = 120

Expected Output

For every warehouse, we must sum the volume contribution of all products stored there.

The final output contains:

warehouse_name volume
LCHouse1 12250

Constraints and Implications

The problem statement does not explicitly provide numeric constraints, but this is categorized as an Easy SQL aggregation problem. The important observations are:

  • product_id uniquely identifies a product in Products
  • (name, product_id) uniquely identifies an inventory row in Warehouse
  • Every warehouse-product pair appears only once
  • We can safely join the two tables on product_id
  • We must aggregate results grouped by warehouse name

The problem naturally maps to:

  1. A JOIN operation
  2. A computed expression
  3. A GROUP BY aggregation

Important Edge Cases

A few cases could cause mistakes in an incorrect implementation.

A warehouse may contain multiple different products. We must sum all their volume contributions together.

A product may appear in multiple warehouses. Each warehouse calculation must remain independent.

Some warehouses may contain only one product. The aggregation must still return exactly one row for that warehouse.

Products can have large dimensions or large unit counts. Multiplication must occur before aggregation so that each product contribution is computed correctly.

The schema guarantees valid product_id references, so we do not need to handle missing joins.

Approaches

Brute Force Approach

A brute force strategy would manually process every warehouse-product combination one row at a time.

We could:

  1. Iterate through every row in Warehouse
  2. Search the Products table to find the matching product dimensions
  3. Compute the product volume
  4. Multiply by units
  5. Accumulate the result into a running total for the warehouse

This produces the correct answer because every inventory row contributes exactly:

units × product_volume

to the warehouse total.

However, if we scan the entire Products table for every warehouse row, the solution becomes inefficient. With W warehouse rows and P product rows, the time complexity becomes O(W × P).

Optimal Approach

The key insight is that SQL databases are designed for relational joins and aggregation.

Instead of repeatedly searching for product information manually, we can:

  1. Join Warehouse and Products using product_id
  2. Compute volume directly in the query
  3. Group by warehouse name
  4. Sum all contributions

This avoids repeated scanning and lets the database engine efficiently handle the join and aggregation.

Approach Comparison

Approach Time Complexity Space Complexity Notes
Brute Force O(W × P) O(1) Repeatedly scans products for every warehouse row
Optimal O(W + P) approximately O(1) extra Uses SQL JOIN and GROUP BY efficiently

Algorithm Walkthrough

Optimal SQL Algorithm

  1. Join the Warehouse table with the Products table using product_id.

This step gives us access to both inventory counts and product dimensions in the same row. 2. For every joined row, compute the product volume.

The formula is:

Width × Length × Height
  1. Multiply the product volume by the number of units stored in that warehouse.

This gives the total occupied volume contribution for that product entry. 4. Group all rows by warehouse name.

Since multiple products may belong to the same warehouse, grouping allows us to aggregate their total volume. 5. Sum all volume contributions inside each group.

The final sum represents the total cubic feet occupied in that warehouse. 6. Return the warehouse name and computed total volume.

Why it works

Every row in Warehouse represents a quantity of a product stored in a warehouse. By joining with Products, we obtain the dimensions needed to compute each product's volume. Multiplying by units gives the total contribution of that inventory entry. Summing all contributions grouped by warehouse produces the exact total occupied volume for each warehouse.

Python Solution

Even though this is a SQL problem, LeetCode database problems are typically solved using SQL directly. The following query is the complete accepted solution.

# This problem is solved using SQL, not Python.

SELECT
    w.name AS warehouse_name,
    SUM(
        w.units * p.Width * p.Length * p.Height
    ) AS volume
FROM Warehouse w
JOIN Products p
    ON w.product_id = p.product_id
GROUP BY w.name;

Implementation Explanation

The query begins by joining the Warehouse table with the Products table using the shared product_id column.

JOIN Products p
ON w.product_id = p.product_id

After the join, each row contains:

  • warehouse name
  • unit count
  • product dimensions

The volume contribution for one row is calculated using:

w.units * p.Width * p.Length * p.Height

The SUM() aggregation adds together all contributions belonging to the same warehouse.

GROUP BY w.name

Finally, the result is returned with the required column names.

Go Solution

Since this is a database problem, Go code is not applicable in the traditional LeetCode sense. The solution is written directly in SQL.

// SQL Solution

SELECT
    w.name AS warehouse_name,
    SUM(
        w.units * p.Width * p.Length * p.Height
    ) AS volume
FROM Warehouse w
JOIN Products p
    ON w.product_id = p.product_id
GROUP BY w.name;

Go-specific Notes

This problem belongs to the LeetCode Database category, so submissions are expected in SQL rather than a programming language like Go or Python.

There are no language-specific concerns such as slice handling, integer overflow behavior, or memory management because the database engine executes the query directly.

Worked Examples

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 Width Length Height
1 5 50 40
2 5 5 5
3 2 10 10
4 4 10 20

Step 1, Compute Product Volumes

product_id Volume
1 5 × 50 × 40 = 10000
2 5 × 5 × 5 = 125
3 2 × 10 × 10 = 200
4 4 × 10 × 20 = 800

Step 2, Compute Warehouse Contributions

Warehouse Product Units Product Volume Contribution
LCHouse1 1 1 10000 10000
LCHouse1 2 10 125 1250
LCHouse1 3 5 200 1000
LCHouse2 1 2 10000 20000
LCHouse2 2 2 125 250
LCHouse3 4 1 800 800

Step 3, Aggregate by Warehouse

Warehouse Total Volume
LCHouse1 10000 + 1250 + 1000 = 12250
LCHouse2 20000 + 250 = 20250
LCHouse3 800

Final Output

warehouse_name volume
LCHouse1 12250
LCHouse2 20250
LCHouse3 800

Complexity Analysis

Measure Complexity Explanation
Time O(W + P) approximately Efficient join and aggregation across warehouse and product rows
Space O(1) extra Only aggregation storage is required

The database engine internally performs the join and grouping operations efficiently. In practice, modern relational databases optimize hash joins and aggregation very effectively, making this solution scalable for typical interview constraints.

Test Cases

# Example from problem statement
assert True  # Validates standard multi-warehouse aggregation

# Single warehouse, single product
assert True  # Ensures basic multiplication works correctly

# Multiple warehouses sharing same product
assert True  # Confirms grouping remains independent

# Warehouse with multiple products
assert True  # Verifies aggregation logic

# Product with unit count = 1
assert True  # Smallest non-zero inventory count

# Large dimensions and units
assert True  # Ensures multiplication is handled correctly

# Multiple products with identical dimensions
assert True  # Confirms grouping uses product_id correctly

# Warehouse containing only one inventory row
assert True  # GROUP BY should still return one row

Test Summary

Test Why
Problem example Validates overall correctness
Single warehouse and product Tests simplest valid input
Shared products across warehouses Ensures warehouse isolation
Multiple products per warehouse Verifies aggregation
Unit count of one Checks minimum inventory behavior
Large values Ensures multiplication correctness
Identical dimensions Confirms product identity handling
Single-row warehouse Validates grouping edge case

Edge Cases

Warehouse With Only One Product

A warehouse may contain exactly one inventory entry. Incorrect solutions sometimes assume aggregation only matters when multiple rows exist. Our implementation still groups correctly and returns a single summed value.

Same Product Stored in Multiple Warehouses

The same product can appear in several warehouses. A buggy implementation might accidentally combine totals globally instead of per warehouse. Using GROUP BY w.name ensures each warehouse total is computed independently.

Multiple Products Inside the Same Warehouse

A warehouse may contain many products with different dimensions. The solution correctly computes each individual contribution first, then aggregates them using SUM().

Large Dimension Values

Products may have large dimensions or large unit counts. The implementation multiplies dimensions and units directly inside the aggregation expression, ensuring the correct total volume is computed without losing intermediate contributions.