This question evaluates SQL data manipulation competencies such as joins and ON vs WHERE semantics, time-window filtering, conditional aggregation for counts and rates, handling nulls, and tie-breaking logic when ranking drivers and computing cancellation metrics.
You work on a rideshare product with airport pickups. Using SQL, answer the questions below.
Assume all timestamps are stored in UTC. Define the analysis window as the last 28 days ending at :as_of_ts (inclusive).
drivers
driver_id
BIGINT (PK)
home_city_id
BIGINT
is_active
BOOLEAN
activated_at
TIMESTAMPTZ
riders
rider_id
BIGINT (PK)
is_active
BOOLEAN
is_fraud
BOOLEAN
first_trip_at
TIMESTAMPTZ
airports
airport_id
BIGINT (PK)
airport_code
TEXT -- e.g., 'SFO'
country_code
TEXT -- e.g., 'US'
trips (one row per trip request)
trip_id
BIGINT (PK)
driver_id
BIGINT (nullable until dispatch)
rider_id
BIGINT
request_at
TIMESTAMPTZ
airport_id
BIGINT (nullable; non-null means airport pickup)
status
TEXT -- one of ('completed','canceled','expired')
canceled_by
TEXT -- nullable; if canceled then one of ('driver','rider','system')
canceled_at
TIMESTAMPTZ (nullable)
Return the top 10 drivers by number of completed airport pickup trips in the last 28 days, restricted to:
airports.country_code = 'US'
)
drivers.is_active = TRUE
)
riders.is_fraud = TRUE
should be excluded)
Output columns:
driver_id
completed_trips
total_airport_requests
(count of all airport trip requests, regardless of status)
completion_rate
=
completed_trips / total_airport_requests
Tie-breakers (in order): higher completed_trips, then higher completion_rate, then lower driver_id.
For the same 28-day window and same US-airport restriction, compute cancellation rates by airport, split by who canceled.
For each airport_code, return:
total_requests
driver_cancel_rate
= driver-canceled trips / total_requests
rider_cancel_rate
= rider-canceled trips / total_requests
system_cancel_rate
= system-canceled trips / total_requests
overall_cancel_rate
= all canceled trips / total_requests
Include only airports with total_requests >= 100.
Note: Write the query in a way that preserves airports with requests even if some joins are missing, and be explicit about which filters belong in ON vs WHERE.