Rider Distance Leaderboard

Easy

A mobility analytics team needs a ranked summary of how far each registered rider has traveled. Table: users id: unique rider identifier name: rider display name Table: rides id: unique ride identifier user_id: rider identifier associated with this trip distance: distance covered in this ride Task: For every rider in users, calculate the total distance across all their rides. Critical requirement: Riders with zero rides must still appear with travelled_distance = 0. Output requirements: Return exactly two columns: name, travelled_distance Sort by travelled_distance in descending order If distances tie, sort by name in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

users:
| id | name   |
|----|--------|
| 1  | aaron  |
| 2  | bella  |
| 3  | chris  |
| 4  | diana  |

rides:
| id | user_id | distance |
|----|---------|----------|
| 10 | 1       | 140      |
| 11 | 2       | 300      |
| 12 | 2       | 20       |
| 13 | 3       | 160      |
| 14 | 1       | 40       |
| 15 | 3       | 20       |

Output

[
  {"name":"bella","travelled_distance":320},
  {"name":"aaron","travelled_distance":180},
  {"name":"chris","travelled_distance":180},
  {"name":"diana","travelled_distance":0}
]

Explanation

bella has the highest total. aaron and chris tie at 180, so alphabetical order places aaron first. diana has no rides and is included with 0.

Example 2

Input

users:
| id | name |
|----|------|
| 10 | ivy  |
| 11 | nora |
| 12 | ryan |

rides:
| id | user_id | distance |
|----|---------|----------|
| (no rows) |

Output

[
  {"name":"ivy","travelled_distance":0},
  {"name":"nora","travelled_distance":0},
  {"name":"ryan","travelled_distance":0}
]

Explanation

No rides exist, so every rider has 0 distance. Ties are broken by name ascending.

Example 3

Input

users:
| id | name |
|----|------|
| 21 | liam |
| 22 | maya |
| 23 | owen |
| 24 | zoe  |

rides:
| id  | user_id | distance |
|-----|---------|----------|
| 100 | 24      | 0        |
| 101 | 21      | 500      |
| 102 | 21      | 250      |
| 103 | 22      | 400      |
| 104 | 22      | 350      |
| 105 | 24      | 900      |

Output

[
  {"name":"zoe","travelled_distance":900},
  {"name":"liam","travelled_distance":750},
  {"name":"maya","travelled_distance":750},
  {"name":"owen","travelled_distance":0}
]

Explanation

zoe leads with 900. liam and maya tie at 750, so liam comes first alphabetically.

Constraints

0 <= number of rows in users <= 200000
0 <= number of rows in rides <= 300000
1 <= users.id <= 1000000000
1 <= rides.id <= 1000000000
1 <= rides.user_id <= 1000000000
0 <= rides.distance <= 1000000000
users.id is unique
rides.id is unique
Each rides.user_id references an existing users.id
Rider names are non-empty strings
Return exactly two columns: name, travelled_distance
Include users with no rides as travelled_distance = 0
Sort by travelled_distance DESC, then name ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with exact columns: name, travelled_distance

users: | id | name | |----|--------| | 1 | aaron | | 2 | bella | | 3 | chris | | 4 | diana | rides: | id | user_id | distance | |----|---------|----------| | 10 | 1 | 140 | | 11 | 2 | 300 | | 12 | 2 | 20 | | 13 | 3 | 160 | | 14 | 1 | 40 | | 15 | 3 | 20 |

users: | id | name | |----|------| | 21 | liam | | 22 | maya | | 23 | owen | | 24 | zoe | rides: | id | user_id | distance | |-----|---------|----------| | 100 | 24 | 0 | | 101 | 21 | 500 | | 102 | 21 | 250 | | 103 | 22 | 400 | | 104 | 22 | 350 | | 105 | 24 | 900 |