- ************************************************************
- ***************************************************************************
Table 1: merchant
A reference table for any service provider such as a restaurant.
col_name | col_type -----------------+------------------- id | bigint marketplace_fee | float price_bucket | varchar(5) delivery_zone_id | bigint is_active | boolean first_online_dt | timestamp city | text country | varchar(2)
Table 2: trip
Fact table for every uber eats delivery.
col_name | col_type --------------------+------------------- trip_id | bigint date | date trip_start_ts | timestamp delivery_fee_usd | int surge_fee_usd | int is_curbside_dropoff | boolean is_completed | boolean is_cash_trip | boolean user_id | bigint restaurant_id | bigint courier_id | bigint city | text country | varchar(2)
Sample results
city | completion_rate
---------------+---------------------
Delhi | 93.1034482758620690
New York | 92.8416485900216920
Berlin | 92.6470588235294118
Seattle | 92.0289855072463768
Solution postgres
SELECT
city,
SUM(CASE WHEN is_completed = TRUE THEN 1 ELSE 0 END) * 100.0/ COUNT(*) AS completion_rate
FROM trip
GROUP BY city
ORDER BY 2 DESC;
Explanation
This query retrieves data from a table called "trip" and calculates the completion rate of trips in each city. The result is displayed in descending order by completion rate.
To do this, the query uses the SUM and COUNT functions to count the number of completed trips and the total number of trips, respectively. It then calculates the completion rate by multiplying the number of completed trips by 100 and dividing it by the total number of trips.
The CASE statement inside the SUM function checks if the "is_completed" column is true or false for each trip. If it is true, it adds 1 to the count of completed trips, otherwise it adds 0.
Finally, the GROUP BY clause groups the results by city, so that the completion rate is calculated for each city separately. The ORDER BY clause orders the results by the second column (completion rate) in descending order.
Expected results
More Uber questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 124 | Active restaurants by city uber | easy |
|
| 125 | Active restaurants with fewer than 5 trips per city uber | easy |
|
| 126 | Top 3 restaurants uber | hard |
|
| 127 | Average rating after 10th trip uber | hard |
|
| 129 | Top 3 and bottom 3 courier uber | medium |
|