✏️ Explanatory Question
Level: Coding Round — A LeetCode HARD problem; combines multi-table joins, conditional aggregation, and filtering out banned users.
The Puzzle: Find the daily cancellation rate of trips where neither the rider nor the driver is banned. The cancellation rate for a day = (cancelled unbanned trips) / (total unbanned trips), rounded to 2 decimals.
| id | client_id | driver_id | status | request_at |
|---|---|---|---|---|
| 1 | 1 | 10 | completed | 2026-08-01 |
| 2 | 2 | 11 | cancelled_by_driver | 2026-08-01 |
| 3 | 3 | 12 | completed | 2026-08-01 |
| 4 | 4 | 13 | cancelled_by_client | 2026-08-01 |
| id | banned | role |
|---|---|---|
| 1 | No | client |
| 4 | Yes | client |
| 10 | No | driver |
Trip 4's client (id 4) is banned, so trip 4 is excluded entirely from both numerator and denominator.
| Day | Cancellation Rate |
|---|---|
| 2026-08-01 | 0.33 |
After removing banned trip 4: 3 valid trips, 1 cancelled (trip 2). Rate = 1/3 = 0.33.
The three-part strategy: (1) Exclude banned users — both the client AND the driver must be unbanned. (2) Count cancellations with conditional aggregation (SUM of a CASE). (3) Divide cancelled by total, per day, rounded to 2 decimals.
SELECT
t.request_at AS 'Day',
ROUND(
SUM(CASE WHEN t.status LIKE 'cancelled%' THEN 1 ELSE 0 END)
/ COUNT(*),
2) AS 'Cancellation Rate'
FROM trips t
JOIN users c ON t.client_id = c.id AND c.banned = 'No' -- unbanned client
JOIN users d ON t.driver_id = d.id AND d.banned = 'No' -- unbanned driver
GROUP BY t.request_at
ORDER BY t.request_at; The key trap — banned filter in the JOIN: By putting c.banned = 'No' and d.banned = 'No' in the JOIN conditions, any trip with a banned client OR driver is dropped from both the numerator and denominator — exactly what the problem requires. If you filtered only cancellations by banned status, the denominator would be wrong.
-- A boolean condition is 1/0, so AVG() directly gives the rate
SELECT
t.request_at AS 'Day',
ROUND(AVG(t.status LIKE 'cancelled%'), 2) AS 'Cancellation Rate'
FROM trips t
JOIN users c ON t.client_id = c.id AND c.banned = 'No'
JOIN users d ON t.driver_id = d.id AND d.banned = 'No'
GROUP BY t.request_at;
-- AVG of (1s and 0s) = fraction that are cancelled = the rate The elegant AVG trick: In MySQL, a comparison like status LIKE 'cancelled%' evaluates to 1 or 0. Averaging those values gives the proportion that are true — i.e., the cancellation rate directly, no explicit division needed. This is a clean, common shortcut for "what fraction meets a condition."
Interviewer follow-up: "The real table has millions of trips across years — how do you keep this fast?" → Add an index on trips(request_at) for the grouping, ensure users(id, banned) supports the join filter, and constrain the date range in a WHERE (e.g., a specific month) so you do not scan all history. For recurring dashboards, pre-aggregate daily rates into a summary table rather than recomputing over raw trips each time.