Interview Guides
You’re working on the analytics platform for a ride-sharing marketplace (think Uber/Lyft scale) operating in 40+ cities with millions of monthly rides. Finance and Driver Ops want a reliable metric for driver revenue to power weekly incentive programs and to detect suspicious payout patterns.
Because ride payments can be split across base fare, surge, tips, and fees—and rides can be canceled or refunded—you need a data model that supports accurate revenue attribution and a query that returns the top earning drivers.
Your query must:
rides.status = 'completed'.base_fare + surge_amount + tip_amount - platform_fee.payments.payment_status <> 'captured' (e.g., failed/voided).driver_id, driver_name, total_revenuetotal_revenue (descending). Break ties by driver_id ascending.drivers| column | type | description |
|---|---|---|
| driver_id | INT | Primary key |
| driver_name | VARCHAR(100) | Driver display name |
| city | VARCHAR(50) | Home city |
| onboarded_at | DATE | Date driver was onboarded |
riders| column | type | description |
|---|---|---|
| rider_id | INT | Primary key |
| rider_name | VARCHAR(100) | Rider name |
| created_at | DATE | Rider signup date |
rides| column | type | description |
|---|---|---|
| ride_id | INT | Primary key |
| driver_id | INT | Foreign key to drivers.driver_id |
| rider_id | INT | Foreign key to riders.rider_id |
| requested_at | TIMESTAMP | When the ride was requested |
| completed_at | TIMESTAMP | When the ride ended (NULL if not completed) |
| status | VARCHAR(20) | e.g., completed, canceled |
payments| column | type | description |
|---|---|---|
| payment_id | INT | Primary key |
| ride_id | INT | Foreign key to rides.ride_id |
| payment_status | VARCHAR(20) | e.g., captured, failed, refunded |
| base_fare | DECIMAL(10,2) | Base fare charged |
| surge_amount | DECIMAL(10,2) | Surge component |
| tip_amount | DECIMAL(10,2) | Tip paid to driver |
| platform_fee | DECIMAL(10,2) | Fee withheld by platform |
| captured_at | TIMESTAMP | When payment was captured |
Only completed rides with captured payments count. That includes rides 301, 302, 304.
| driver_id | driver_name | total_revenue |
|---|---|---|
| 101 | Ava Chen | 60.00 |
| 102 | Mateo Ruiz | 15.00 |
