128. Completion rate

easy uber

  • ************************************************************
  • ***************************************************************************

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

    SUM(CASE WHEN is_completed = TRUE THEN 1 ELSE 0 END) * 100.0/ COUNT(*) AS completion_rate
FROM trip


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