Google Google Maps Interview Question 01

Ride
Completion
Rate

Calculate the completion rate of rides per city.

Table Schema

Inspect
Table

interactive

Sample Data

Sample Input: rides

ride_id	user_id	city	status
1	101	NYC	completed
2	102	NYC	cancelled
3	103	SF	completed
4	104	SF	cancelled
5	105	NYC	completed
6	106	SF	completed
7	107	NYC	cancelled
8	108	NYC	completed

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: Count completed rides per city.

Hint 02: Divide by total rides in that city.

Hint 03: Use 100.0 or a decimal cast to avoid integer division.

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 rides
GROUP BY city;

Explanation

Group rides by city.

Count completed rides with a CASE expression.

Divide completed rides by all rides and round the percentage.