Uber Uber Interview Question 01

Trip
Completion
Rate

Calculate the trip completion rate per city.

Table Schema

Inspect
Table

interactive

Sample Data

Sample Input: trips

trip_id	driver_id	rider_id	city	status
1	201	101	NYC	completed
2	202	102	NYC	cancelled
3	203	103	SF	completed
4	204	104	SF	completed
5	205	105	NYC	completed
6	206	106	SF	cancelled
7	207	107	NYC	completed
8	208	108	NYC	cancelled

Expected Output

city	completion_rate
NYC	60.00
SF	66.67

SQL Editor

Waiting for query

city	completion_rate
NYC	60.00
SF	66.67

Hints

Hint 01: Identify the grouping level required by the output.

Hint 02: Aggregate with COUNT, SUM, AVG, or a window function as needed.

Hint 03: Filter after aggregation with HAVING or after ranking with an outer query.

Solution

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT city, ROUND(100.0 * SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS completion_rate
FROM trips
GROUP BY city;

Explanation

Read the expected output columns to determine the final grain.

Aggregate or rank the input rows to calculate the requested metric.

Filter, sort, and alias the final columns to match the output.