Trips and Cancellation Rate

Problem

Schema: Trips(id INT, client_id INT, status VARCHAR, trip_date DATE). status is one of completed, cancelled_by_driver, cancelled_by_client. Return each trip_date and the cancellation rate (cancelled / total), rounded to 2 decimals.

Constraints

• A trip with any cancelled_* status counts as cancelled.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

SELECT trip_date,
       ROUND(
         SUM(CASE WHEN status LIKE 'cancelled%' THEN 1 ELSE 0 END) * 1.0
         / COUNT(*), 2
       ) AS cancellation_rate
FROM Trips
GROUP BY trip_date
ORDER BY trip_date;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.