Total Stored Capacity Per Cold Room
Problem
A food-distribution company stores crates of produce in several cold rooms. For each room it knows how many crates of each produce type are stacked there, and the three box dimensions of a single crate of every produce type. Report the total occupied capacity of each cold room, where one crate of a type occupies `dim_x_mm * dim_y_mm * dim_z_mm` cubic millimetres.
`RoomStacks`:
- `room_label` (varchar) — cold room name
- `produce_id` (int) — produce type reference
- `crate_qty` (int) — number of crates of that type stacked in the room
`Produce`:
- `produce_id` (int) — unique produce type id
- `produce_label` (varchar)
- `dim_x_mm` (int), `dim_y_mm` (int), `dim_z_mm` (int) — dimensions of one crate
Return each cold room with its total occupied capacity. Rows may be in any order.
Tables
Example rows — the live problem includes the full dataset.
| room_label | produce_id | crate_qty |
|---|---|---|
| Chiller-North | 1 | 1 |
| Chiller-North | 2 | 10 |
| Chiller-North | 3 | 5 |
| produce_id | produce_label | dim_x_mm | dim_y_mm | dim_z_mm |
|---|---|---|---|---|
| 1 | Crisp Apples | 5 | 50 | 40 |
| 2 | Cherry Tomatoes | 5 | 5 | 5 |
| 3 | Leaf Spinach | 2 | 10 | 10 |
Expected output
Your answer should return 3 rows with the columns cold_room, total_capacity.
Starter code (SQL)
SELECT *
FROM RoomStacks;Solve this SQL question free
Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.
Solution & explanation
Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.
Sign up free to unlock