How can you write a SQL query to find the driver with the lowest delivery efficiency (deliveries per time spent driving) in the past two months?

Question

Using a dataset that contains information on a food delivery service, how can we construct a SQL query to determine which driver has the lowest efficiency, measured by the number of deliveries completed per unit of driving time, over the past two months?

Accepted Answer

To write a SQL query that finds the driver with the lowest delivery efficiency (deliveries per unit of driving time) in the past two months, you will need a dataset that includes at least the following information for each delivery: driver ID, delivery completion date, number of deliveries, and time spent driving.

Here is a general outline of the steps needed to construct the query:

1. **Filter the Data for the Past Two Months:** You need to filter the data to only include records from the past two months. This can typically be done using the `WHERE` clause combined with a date function to calculate the date range.

2. **Aggregate Data by Driver:** You should group the data by driver, calculating the total number of deliveries and total driving time for each driver in the specified timeframe.

3. **Calculate Delivery Efficiency:** Compute the delivery efficiency by dividing the total number of deliveries by the total driving time for each driver.

4. **Identify the Driver with the Lowest Efficiency:** Once you have the efficiencies computed for each driver, use an `ORDER BY` clause to sort these in ascending order and limit the result to identify the driver with the lowest efficiency.

Here is a sample SQL query implementing these steps:

```sql
WITH DriverStats AS (
    SELECT 
        driver_id,
        SUM(deliveries_count) AS total_deliveries,
        SUM(time_spent_driving) AS total_driving_time
    FROM 
        deliveries
    WHERE 
        delivery_date >= DATEADD(MONTH, -2, CURRENT_DATE)  -- Assuming the use of a SQL dialect that supports DATEADD, adjust as needed
    GROUP BY 
        driver_id
)
SELECT 
    driver_id,
    total_deliveries,
    total_driving_time,
    (total_deliveries / NULLIF(total_driving_time, 0)) AS delivery_efficiency
FROM 
    DriverStats
ORDER BY 
    delivery_efficiency ASC
LIMIT 1;
```

### Explanation:

- **`WITH DriverStats` CTE:** This Common Table Expression calculates the total deliveries and total driving time for each driver over the past two months.
- **`SUM()` Function:** Aggregates delivery counts and driving times.
- **`DATEADD(MONTH, -2, CURRENT_DATE)`:** Filters the dataset to include only records from the past two months. Adjust the date function based on the SQL dialect you are using (e.g., `DATE_SUB` in MySQL or `CURRENT_TIMESTAMP - INTERVAL '2 MONTH'` in PostgreSQL).
- **`NULLIF`:** Used to avoid division by zero errors by returning `NULL` when `total_driving_time` is zero.
- **`ORDER BY ... ASC LIMIT 1`:** Sorts the results by delivery efficiency in ascending order and limits the output to the top result, which is the driver with the lowest efficiency.

Ensure the actual column names (e.g., `deliveries_count`, `time_spent_driving`, and `delivery_date`) match those in your dataset, and adjust date logic according to your SQL environment.

How can you write a SQL query to find the driver with the lowest delivery efficiency (deliveries per time spent driving) in the past two months?

Question Explain

Answer Example

Related Interview Questions