How can I write a SQL query to calculate the total rides and deliveries completed per zip code?

Question

Can you provide a detailed SQL query to calculate the total number of rides and deliveries completed for each zip code in a dataset associated with a food delivery service? The query should sum both rides and deliveries separately for every zip code present in the dataset.

Accepted Answer

To calculate the total rides and deliveries completed per zip code using SQL, you'll need a dataset that contains information about rides and deliveries. Typically, this dataset would include columns such as `zip_code`, `ride_id`, and `delivery_id` that track unique instances of rides and deliveries.

Assuming we have a table named `transportation_data` with relevant columns (`zip_code`, `ride_id`, `delivery_id`), we can write a SQL query to calculate the totals. Here's how you can structure your SQL query:

```sql
SELECT 
    zip_code,
    COUNT(DISTINCT ride_id) AS total_rides,
    COUNT(DISTINCT delivery_id) AS total_deliveries
FROM 
    transportation_data
GROUP BY 
    zip_code
ORDER BY 
    zip_code;
```

### Explanation:

- **SELECT zip_code**: We select the `zip_code` column to group our results by each unique zip code.

- **COUNT(DISTINCT ride_id) AS total_rides**: We count the distinct `ride_id` values to find out how many rides were completed per zip code. Using `DISTINCT` ensures that each ride is counted only once, even if there are duplicates in the data.

- **COUNT(DISTINCT delivery_id) AS total_deliveries**: Similarly, we count the distinct `delivery_id` values to determine the total number of deliveries per zip code.

- **FROM transportation_data**: This specifies the table from which we are retrieving the data.

- **GROUP BY zip_code**: This groups the results by zip code, so the counts are calculated separately for each zip code.

- **ORDER BY zip_code**: This orders the results by zip code, which can make the results easier to read and interpret.

This query will give you a list of each zip code along with the total number of rides and deliveries completed there. Adjust the table and column names according to your actual database schema to ensure the query runs correctly.

How can I write a SQL query to calculate the total rides and deliveries completed per zip code?

Question Explain

Answer Example

Related Interview Questions