Warehouse Storage Volume by Facility

Easy

A logistics analytics platform tracks inventory placement across multiple fulfillment facilities. Table: warehouse name: facility identifier product_id: stored product identifier units: number of units of that product stored in the facility (name, product_id) is unique Table: products product_id: unique product identifier product_name: descriptive product label width: product width in feet length: product length in feet height: product height in feet Metric definition: Per-row occupied cubic volume = units * width * length * height. Facility total volume = sum of per-row occupied volumes for all rows of that facility. Task: Return the occupied inventory volume for each facility. Output requirements: Return columns in this exact order: warehouse_name volume Sort by warehouse_name ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

warehouse:
| name        | product_id | units |
|-------------|------------|-------|
| NorthHub    | 1          | 1     |
| NorthHub    | 2          | 10    |
| NorthHub    | 3          | 5     |
| SouthHub    | 1          | 2     |
| SouthHub    | 2          | 2     |
| OverflowHub | 4          | 1     |

products:
| product_id | product_name | width | length | height |
|------------|--------------|-------|--------|--------|
| 1          | Console-TV   | 5     | 50     | 40     |
| 2          | KeyFob       | 5     | 5      | 5      |
| 3          | PhoneBox     | 2     | 10     | 10     |
| 4          | ApparelPack  | 4     | 10     | 20     |

Output

[
  {"warehouse_name":"NorthHub","volume":12250},
  {"warehouse_name":"OverflowHub","volume":800},
  {"warehouse_name":"SouthHub","volume":20250}
]

Explanation

Each row contributes units multiplied by product cubic size. Summing per facility yields 12250 for NorthHub, 20250 for SouthHub, and 800 for OverflowHub.

Example 2

Input

warehouse:
| name | product_id | units |
|------|------------|-------|
| (no rows) |

products:
| product_id | product_name | width | length | height |
|------------|--------------|-------|--------|--------|
| 10         | SparePart-A  | 2     | 3      | 4      |
| 11         | SparePart-B  | 5     | 6      | 7      |

Output

[]

Explanation

No warehouse rows means no facility has stored inventory, so the result is empty.

Example 3

Input

warehouse:
| name      | product_id | units |
|-----------|------------|-------|
| East-01   | 10         | 3     |
| East-01   | 20         | 4     |
| West-Prime| 10         | 2     |
| West-Prime| 30         | 1     |
| Edge Lab  | 40         | 5     |

products:
| product_id | product_name   | width | length | height |
|------------|----------------|-------|--------|--------|
| 10         | Relay Module   | 3     | 3      | 3      |
| 20         | Cooling Unit   | 2     | 5      | 4      |
| 30         | Battery Crate  | 10    | 2      | 2      |
| 40         | Medical Cooler | 1     | 8      | 6      |

Output

[
  {"warehouse_name":"East-01","volume":241},
  {"warehouse_name":"Edge Lab","volume":240},
  {"warehouse_name":"West-Prime","volume":94}
]

Explanation

Per-row contributions are East-01: 3*27 + 4*40 = 241, West-Prime: 2*27 + 1*40 = 94, and Edge Lab: 5*48 = 240.

Constraints

0 <= number of rows in warehouse <= 400000
1 <= number of rows in products <= 200000
(name, product_id) is unique in warehouse
product_id is unique in products
warehouse.product_id references products.product_id
1 <= units <= 10000
1 <= width, length, height <= 2000
All intermediate and final volume values fit signed 64-bit integer range
Return exactly two columns: warehouse_name and volume
Sort output by warehouse_name ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns warehouse_name and volume

warehouse: | name | product_id | units | |-------------|------------|-------| | NorthHub | 1 | 1 | | NorthHub | 2 | 10 | | NorthHub | 3 | 5 | | SouthHub | 1 | 2 | | SouthHub | 2 | 2 | | OverflowHub | 4 | 1 | products: | product_id | product_name | width | length | height | |------------|--------------|-------|--------|--------| | 1 | Console-TV | 5 | 50 | 40 | | 2 | KeyFob | 5 | 5 | 5 | | 3 | PhoneBox | 2 | 10 | 10 | | 4 | ApparelPack | 4 | 10 | 20 |

warehouse: | name | product_id | units | |------|------------|-------| | (no rows) | products: | product_id | product_name | width | length | height | |------------|--------------|-------|--------|--------| | 10 | SparePart-A | 2 | 3 | 4 | | 11 | SparePart-B | 5 | 6 | 7 |

warehouse: | name | product_id | units | |-----------|------------|-------| | East-01 | 10 | 3 | | East-01 | 20 | 4 | | West-Prime| 10 | 2 | | West-Prime| 30 | 1 | | Edge Lab | 40 | 5 | products: | product_id | product_name | width | length | height | |------------|----------------|-------|--------|--------| | 10 | Relay Module | 3 | 3 | 3 | | 20 | Cooling Unit | 2 | 5 | 4 | | 30 | Battery Crate | 10 | 2 | 2 | | 40 | Medical Cooler | 1 | 8 | 6 |