Business Context
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.
Task
- Review the proposed relational data model below (drivers, riders, rides, payments).
- Write a SQL query to find the top 3 drivers by revenue over completed rides.
Requirements
Your query must:
- Count revenue only for rides with
rides.status = 'completed'.
- Define driver revenue as:
base_fare + surge_amount + tip_amount - platform_fee.
- Exclude any payment rows where
payments.payment_status <> 'captured' (e.g., failed/voided).
- Aggregate revenue at the driver level and return:
driver_id, driver_name, total_revenue
- Return exactly the top 3 drivers by
total_revenue (descending). Break ties by driver_id ascending.
Table Definitions
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 |
Sample Data
drivers
| driver_id | driver_name | city | onboarded_at |
|---|
| 101 | Ava Chen | Seattle | 2023-05-10 |
| 102 | Mateo Ruiz | Seattle | 2023-07-22 |
| 103 | Priya Nair | San Francisco | 2023-03-01 |
| 104 | Jordan Kim | San Francisco | 2024-01-15 |
riders
| rider_id | rider_name | created_at |
|---|
| 201 | Sam Patel | 2023-11-02 |
| 202 | Lina Gomez | 2024-02-10 |
| 203 | Chris Wong | 2024-03-05 |
rides
| ride_id | driver_id | rider_id | requested_at | completed_at | status |
|---|
| 301 | 101 | 201 | 2024-06-01 08:10:00 | 2024-06-01 08:35:00 | completed |
| 302 | 102 | 202 | 2024-06-01 09:00:00 | 2024-06-01 09:22:00 | completed |
| 303 | 103 | 203 | 2024-06-02 18:10:00 | NULL | canceled |
| 304 | 101 | 202 | 2024-06-03 20:00:00 | 2024-06-03 20:40:00 | completed |
| 305 | 104 | 201 | 2024-06-04 07:30:00 | 2024-06-04 07:55:00 | completed |
payments
| payment_id | ride_id | payment_status | base_fare | surge_amount | tip_amount | platform_fee | captured_at |
|---|
| 401 | 301 | captured | 22.00 | 3.00 | 5.00 | 6.00 | 2024-06-01 08:36:00 |
| 402 | 302 | captured | 18.00 | 0.00 | 2.00 | 5.00 | 2024-06-01 09:23:00 |
| 403 | 303 | captured | 12.00 | 0.00 | 0.00 | 3.00 | 2024-06-02 18:12:00 |
| 404 | 304 | captured | 35.00 | 10.00 | 0.00 | 9.00 | 2024-06-03 20:41:00 |
| 405 | 305 | failed | 20.00 | 2.00 | 1.00 | 5.00 | 2024-06-04 07:56:00 |
Expected Output (for sample data)
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 |
