You work at a ride-hailing company and are analyzing orders (trips) between drivers and riders.
driversdriver_id
BIGINT (PK)
home_city
TEXT
created_at
TIMESTAMP
ridersrider_id
BIGINT (PK)
home_city
TEXT
created_at
TIMESTAMP
ordersorder_id
BIGINT (PK)
driver_id
BIGINT (FK →
drivers.driver_id
, nullable if unassigned)
rider_id
BIGINT (FK →
riders.rider_id
)
requested_at
TIMESTAMP
accepted_at
TIMESTAMP (nullable)
completed_at
TIMESTAMP (nullable)
canceled_at
TIMESTAMP (nullable)
status
TEXT
'requested' | 'accepted' | 'completed' | 'canceled'
canceled_by
TEXT (nullable)
'rider' | 'driver' | 'system'
cancel_reason
TEXT (nullable)
pickup_type
TEXT
'airport' | 'city'
airport_code
TEXT (nullable)
fare_usd
NUMERIC (nullable)
status='canceled'
(equivalently
canceled_at IS NOT NULL
).
status='completed'
.
Write SQL queries for the following. Each query should clearly state any additional assumptions you make.
driver_id
,
completed_orders_30d
canceled_orders / total_requested_orders
within the segment.
canceled_by
(include an explicit bucket like
'not_canceled'
if you choose),
total_orders
,
canceled_orders
,
cancellation_rate
rider_id
,
canceled_orders_30d
Note: Many candidates make mistakes around filtering joined tables (e.g., conditions placed in
WHEREvsON). Write queries that handle nulls/joins correctly.