Logistics: Count Shipments by Status

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The operations manager needs a live shipment status board — how many shipments are in each status right now?

Tables

Table: Drivers

| driver_id | name | phone | vehicle_no |
| --- | --- | --- | --- |
| 1 | Raj | 9876543210 | DL01AB1234 |
| 2 | Atul | 9123456780 | MH12CD5678 |
| 3 | Sudheer | 9988776655 | KA03EF9012 |
| 4 | Krishna | 8899001122 | TS07GH3456 |

Table: Shipments

| shipment_id | origin | destination | weight_kg | status |
| --- | --- | --- | --- | --- |
| 101 | Delhi | Mumbai | 1200 | Delivered |
| 102 | Hyderabad | Bangalore | 800 | In Transit |
| 103 | Chennai | Kolkata | 1500 | Delivered |
| 104 | Pune | Delhi | 600 | In Transit |
| 105 | Jaipur | Ahmedabad | 1100 | Pending |

Table: Deliveries

| delivery_id | shipment_id | driver_id | delivery_date | distance_km | freight_amount |
| --- | --- | --- | --- | --- | --- |
| 501 | 101 | 1 | 2024-05-01 | 1480 | 8500.00 |
| 502 | 102 | 2 | 2024-05-02 | 580 | 4200.00 |
| 503 | 103 | 3 | 2024-05-03 | 1670 | 9300.00 |
| 504 | 104 | 4 | 2024-05-04 | 1210 | 6900.00 |
| 505 | 105 | 2 | 2024-05-05 | 910 | 5100.00 |

Expected Output

| status | shipment_count |
| --- | --- |
| Delivered | 2 |
| In Transit | 2 |
| Pending | 1 |

Return: status, total_shipments
Sort by total_shipments descending