Ride Cancellation Rate

A ride-hailing company suspects suspended accounts distort its cancellation numbers. Compute the daily cancellation rate from 1 May 2026 to 3 May 2026 inclusive, counting only ride requests where both the rider and the driver have account_status = 'active'.

A request counts as cancelled if its outcome is 'cancelled_by_rider' or 'cancelled_by_driver'. The rate for a day is cancelled eligible requests divided by all eligible requests on that day (by the date part of requested_at), rounded to 2 decimal places as a fraction (0.33, not 33).

Columns: ride_date (YYYY-MM-DD), cancellation_rate, ordered by ride_date. Leave out days with no eligible requests; return no rows if there are none at all.

Tables

riders — One row per rider account.

drivers — One row per driver account. Driver ids and rider ids are separate numbering schemes.

ride_requests — One row per ride request.

Example

riders

drivers

ride_requests

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Product Analytics · problem 69 of 70 · SQLite

The idea

Three things have to be right: which requests are eligible, which days are in range, and how the ratio is computed.

Eligibility. Inner join ride_requests to riders and to drivers, with account_status = 'active' in each join condition. A request survives only if both parties are active. Riders and drivers live in separate tables with separate ids, so each id must be checked against its own table.

Date range. requested_at is a timestamp, so BETWEEN '2026-05-01' AND '2026-05-03' on the raw value ends at midnight on 3 May. Compare date(requested_at), or use the half-open range >= '2026-05-01' AND < '2026-05-04'.

The ratio. Group by day and take AVG of a flag that is 1.0 for a cancellation and 0 otherwise. That equals cancelled/total and is already a real number, then ROUND(..., 2). Days with no eligible request never form a group, so they are left out automatically.

Solution

SELECT
  date(q.requested_at) AS ride_date,
  ROUND(
    AVG(CASE WHEN q.outcome IN ('cancelled_by_rider', 'cancelled_by_driver') THEN 1.0 ELSE 0 END),
    2
  ) AS cancellation_rate
FROM ride_requests AS q
JOIN riders  AS r ON r.rider_id  = q.rider_id  AND r.account_status = 'active'
JOIN drivers AS d ON d.driver_id = q.driver_id AND d.account_status = 'active'
WHERE date(q.requested_at) BETWEEN '2026-05-01' AND '2026-05-03'
GROUP BY date(q.requested_at)
ORDER BY ride_date;

Another way

SELECT
  substr(requested_at, 1, 10) AS ride_date,
  ROUND(1.0 * SUM(outcome <> 'completed') / COUNT(*), 2) AS cancellation_rate
FROM ride_requests
WHERE requested_at >= '2026-05-01'
  AND requested_at <  '2026-05-04'
  AND rider_id  NOT IN (SELECT rider_id  FROM riders  WHERE account_status = 'suspended')
  AND driver_id NOT IN (SELECT driver_id FROM drivers WHERE account_status = 'suspended')
GROUP BY ride_date
ORDER BY ride_date;

Why common answers fail

BETWEEN on the raw timestamp stops at '2026-05-03' (midnight), so almost all of 3 May is dropped.

SELECT date(q.requested_at) AS ride_date,
       ROUND(AVG(CASE WHEN q.outcome IN ('cancelled_by_rider', 'cancelled_by_driver') THEN 1.0 ELSE 0 END), 2) AS cancellation_rate
FROM ride_requests q
JOIN riders r ON r.rider_id = q.rider_id AND r.account_status = 'active'
JOIN drivers d ON d.driver_id = q.driver_id AND d.account_status = 'active'
WHERE q.requested_at BETWEEN '2026-05-01' AND '2026-05-03'
GROUP BY date(q.requested_at)
ORDER BY ride_date;

Only checks the rider, so requests served by suspended drivers stay in the numbers.

SELECT date(q.requested_at) AS ride_date,
       ROUND(AVG(CASE WHEN q.outcome IN ('cancelled_by_rider', 'cancelled_by_driver') THEN 1.0 ELSE 0 END), 2) AS cancellation_rate
FROM ride_requests q
JOIN riders r ON r.rider_id = q.rider_id AND r.account_status = 'active'
WHERE date(q.requested_at) BETWEEN '2026-05-01' AND '2026-05-03'
GROUP BY date(q.requested_at)
ORDER BY ride_date;

Integer division: the cancelled count divided by the total is 0 unless every request was cancelled.

SELECT date(q.requested_at) AS ride_date,
       ROUND(SUM(CASE WHEN q.outcome <> 'completed' THEN 1 ELSE 0 END) / COUNT(*), 2) AS cancellation_rate
FROM ride_requests q
JOIN riders r ON r.rider_id = q.rider_id AND r.account_status = 'active'
JOIN drivers d ON d.driver_id = q.driver_id AND d.account_status = 'active'
WHERE date(q.requested_at) BETWEEN '2026-05-01' AND '2026-05-03'
GROUP BY date(q.requested_at)
ORDER BY ride_date;

Looks up drivers in the riders table; the ids are separate numbering schemes, so the wrong requests get excluded.

SELECT date(q.requested_at) AS ride_date,
       ROUND(AVG(CASE WHEN q.outcome IN ('cancelled_by_rider', 'cancelled_by_driver') THEN 1.0 ELSE 0 END), 2) AS cancellation_rate
FROM ride_requests q
WHERE date(q.requested_at) BETWEEN '2026-05-01' AND '2026-05-03'
  AND q.rider_id NOT IN (SELECT rider_id FROM riders WHERE account_status = 'suspended')
  AND q.driver_id NOT IN (SELECT rider_id FROM riders WHERE account_status = 'suspended')
GROUP BY date(q.requested_at)
ORDER BY ride_date;

What the interviewer is checking

This checks careful filtering with two joins, timestamp range boundaries and ratio arithmetic. Each one is a quiet way to ship a wrong metric.

Mistakes to avoid

• Filtering only the rider (or only the driver) for suspension.
• BETWEEN on a timestamp column, which silently drops the last day.
• Integer division producing 0.
• Joining driver_id to the riders table.

Follow-ups to expect

• Show days with no eligible rides as 0 or NULL (generate the dates with a recursive CTE and LEFT JOIN).
• Split the rate into rider vs driver cancellations (two AVG(CASE ...) columns).
• Exclude accounts only for rides after their suspension date (store suspended_at and compare it with requested_at).

In real work

Marketplace health dashboards report cancellation, refund and dispute rates with fraud or suspended accounts filtered out so the trend reflects real customers.

Practice next