Write SQL for top drivers and cancellation rates

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).

Tables

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)

Q1: Top drivers by airport completions

Return the top 10 drivers by number of completed airport pickup trips in the last 28 days, restricted to:

• airports in the US ( airports.country_code = 'US' )
• active drivers only ( drivers.is_active = TRUE )
• exclude fraudulent riders ( 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.

Q2: Cancellation rate by airport and canceler

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.