Logistics: Total Freight Earned Per Driver

SQL coding challenge · Difficulty: easy · +50 XP

Problem

A logistics company pays drivers per delivery. Calculate the total freight each driver has earned.

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

| name | total_freight |
| --- | --- |
| Atul | 9300.00 |
| Sudheer | 9300.00 |
| Raj | 8500.00 |
| Krishna | 6900.00 |

Return: driver_name, total_freight
Only drivers with at least one delivery
Sort by total_freight descending