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…