Warehouse Shipment Summary
You are managing a logistics database containing information about warehouses and their associated shipments. You need to write a query that lists each warehouse along with the total weight of all shipments currently assigned to it.
Tables
warehouses:id(integer, primary key),name(text),location(text).shipments:id(integer, primary key),warehouse_id(integer, foreign key referencingwarehouses.id),tracking_number(text),weight_kg(numeric).
Task
Write a query that returns the warehouse name, location, and the total shipment weight as total_weight. If a warehouse has no shipments, its total weight should be reported as 0 (not null). Sort the results by total_weight in descending order, and then by warehouse name alphabetically for any ties.
Example
For a warehouse named 'North Hub' in 'Chicago' with two shipments weighing 15.5 and 24.0 kg, and an empty warehouse named 'South Depot' in 'Dallas', the output should be:
| name | location | total_weight |
|---|---|---|
| North Hub | Chicago | 39.5 |
| South Depot | Dallas | 0.0 |
| name | location | total_weight |
|---|---|---|
| South Depot | Dallas | 100 |
| North Hub | Chicago | 57.75 |
| East Port | Boston | 0 |
Submitting also runs your answer against 3 hidden datasets, each built around an edge case: NULLs, ties, empty tables. A failure names the case without showing its data.
Follow-up: How would you modify this query to only return warehouses where the total weight exceeds 100 kg?
Return three columns: `name`, `location`, and `total_weight`. `total_weight` must be numeric. Warehouses with no shipments must show `0` or `0.0`. Sort rows primarily by `total_weight` descending, then by `name` ascending.
- Accepted
- 2/2
- Acceptance Rate
- 100.0%
- Views
- 12