User Total Ride Distance Report

Easy

A mobility analytics team needs a daily rider summary that shows how far each registered user has traveled. Table: Users user_id: unique identifier of a platform user name: user display name Table: Rides ride_id: unique identifier of a ride event user_id: user who took the ride distance: distance covered in that ride Distance rule: A user's traveled_distance equals the sum of distance across all rides linked to that user. Output requirements: Return one row per user. Include users who have no rides. For users without rides, traveled_distance must be 0. Return exactly these columns in this order: user_id name traveled_distance Return rows ordered by user_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Users:
| user_id | name    |
|---------|---------|
| 17      | Addison |
| 14      | Ethan   |
| 4       | Michael |
| 2       | Avery   |
| 10      | Eleanor |

Rides:
| ride_id | user_id | distance |
|---------|---------|----------|
| 72      | 17      | 160      |
| 42      | 14      | 161      |
| 45      | 4       | 59       |
| 32      | 2       | 197      |
| 15      | 4       | 357      |
| 56      | 2       | 196      |
| 10      | 14      | 25       |

Output

[
  {"user_id":2,"name":"Avery","traveled_distance":393},
  {"user_id":4,"name":"Michael","traveled_distance":416},
  {"user_id":10,"name":"Eleanor","traveled_distance":0},
  {"user_id":14,"name":"Ethan","traveled_distance":186},
  {"user_id":17,"name":"Addison","traveled_distance":160}
]

Explanation

Distances are summed per user from Rides. User 10 has no matching ride rows, so traveled_distance is 0.

Example 2

Input

Users:
| user_id | name   |
|---------|--------|
| 1       | Noor   |
| 2       | Lucia  |
| 3       | Kenji  |

Rides:
| ride_id | user_id | distance |
|---------|---------|----------|
| 100     | 1       | 40       |
| 101     | 1       | 60       |
| 102     | 2       | 0        |

Output

[
  {"user_id":1,"name":"Noor","traveled_distance":100},
  {"user_id":2,"name":"Lucia","traveled_distance":0},
  {"user_id":3,"name":"Kenji","traveled_distance":0}
]

Explanation

User 1 has two rides totaling 100. User 2 has a ride of distance 0. User 3 has no rides and still appears with 0.

Example 3

Input

Users:
| user_id | name |
|---------|------|
| 9       | Mira |

Rides:
| ride_id | user_id | distance |
|---------|---------|----------|

Output

[
  {"user_id":9,"name":"Mira","traveled_distance":0}
]

Explanation

The user table is the driving set, so a user with no rides is still returned with a zero total.

Constraints

0 <= number of rows in Users <= 200000
0 <= number of rows in Rides <= 500000
Users.user_id values are unique
Rides.ride_id values are unique
1 <= user_id <= 1000000000
1 <= ride_id <= 1000000000
0 <= distance <= 1000000
Each Rides.user_id references an existing Users.user_id
Return exactly columns: user_id, name, traveled_distance
Return one row per user
Output must be sorted by user_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: user_id, name, traveled_distance

Users: | user_id | name | |---------|---------| | 17 | Addison | | 14 | Ethan | | 4 | Michael | | 2 | Avery | | 10 | Eleanor | Rides: | ride_id | user_id | distance | |---------|---------|----------| | 72 | 17 | 160 | | 42 | 14 | 161 | | 45 | 4 | 59 | | 32 | 2 | 197 | | 15 | 4 | 357 | | 56 | 2 | 196 | | 10 | 14 | 25 |

[ {"user_id":2,"name":"Avery","traveled_distance":393}, {"user_id":4,"name":"Michael","traveled_distance":416}, {"user_id":10,"name":"Eleanor","traveled_distance":0}, {"user_id":14,"name":"Ethan","traveled_distance":186}, {"user_id":17,"name":"Addison","traveled_distance":160} ]

Users: | user_id | name | |---------|--------| | 1 | Noor | | 2 | Lucia | | 3 | Kenji | Rides: | ride_id | user_id | distance | |---------|---------|----------| | 100 | 1 | 40 | | 101 | 1 | 60 | | 102 | 2 | 0 |