Drill 6 of 7 in Ride-sharing questions
0

Interview Question Bank · One table joined twice · NULL foreign keys · FILTER

6. Cancellation rate by who cancelled, banned users excluded

HardRide-sharingQuestion #285

Concept: One table joined twice · NULL foreign keys · FILTERshow

When a fact table points to the same dimension twice (rider and driver), join it twice under two aliases. Be careful if one key can be NULL: an inner join drops those rows, and d.banned = false (or NOT d.banned) is NULL, not true, for them. LEFT JOIN and write the test as d.banned IS NOT TRUE, so a missing driver passes.

Example (different data):

SELECT f.route, COUNT(*) AS flights
FROM flights f
JOIN crew c ON c.id = f.captain_id
LEFT JOIN crew fo ON fo.id = f.first_officer_id
WHERE NOT c.suspended
  AND fo.suspended IS NOT TRUE
GROUP BY f.route;

Your task

Corvo Rides' trust and safety team is reviewing June 2025 cancellations by city, split by who cancelled. Trips involving a banned account, rider or driver, are fraud suspects and must be left out. A rider can cancel before any driver accepts, which leaves driver_id empty.

Leaving out every trip whose rider or driver is banned, return city, trips, rider_cancel_pct (status 'cancelled_by_rider' as a % of trips) and driver_cancel_pct (status 'cancelled_by_driver' as a % of trips), both rounded to 1 decimal. Trips with no driver count normally. Sort by city.

Starting Postgres in your browser…

Checking your access…