Top Travellers - Problem

You are given two tables: Users and Rides.

The Users table contains information about users with columns id (unique identifier) and name (user's name).

The Rides table contains ride information with columns id (unique identifier), user_id (foreign key referencing Users.id), and distance (distance traveled in that ride).

Task: Write a SQL query to report the total distance traveled by each user.

Requirements:

  • Return results ordered by travelled_distance in descending order
  • If two or more users have the same total distance, order them by name in ascending order
  • Include users who haven't taken any rides (with 0 total distance)

Table Schema

Users
Column Name Type Description
id PK int Unique user identifier
name varchar Name of the user
Primary Key: id
Rides
Column Name Type Description
id PK int Unique ride identifier
user_id int Foreign key referencing Users.id
distance int Distance traveled in this ride
Primary Key: id

Input & Output

Example 1 — Users with Different Travel Distances
Input Tables:
Users
id name
1 Alice
2 Bob
3 Alex
4 Donald
7 Lee
13 Jonathan
19 Elvis
Rides
id user_id distance
1 1 120
2 2 317
3 3 222
4 7 100
5 13 312
6 19 50
7 7 120
8 19 400
9 7 230
Output:
name travelled_distance
Elvis 450
Lee 450
Bob 317
Jonathan 312
Alex 222
Alice 120
Donald 0
💡 Note:

Alice traveled 120 km total. Bob traveled 317 km. Alex traveled 222 km. Donald had no rides (0 km). Lee traveled 100+120+230=450 km. Jonathan traveled 312 km. Elvis traveled 50+400=450 km. Results are ordered by distance descending, with Elvis and Lee (both 450 km) ordered alphabetically by name.
Example 2 — Users with No Rides
Input Tables:
Users
id name
1 Alice
2 Bob
Rides
id user_id distance
Output:
name travelled_distance
Alice 0
Bob 0
💡 Note:

When no rides exist, all users have 0 total distance and are ordered alphabetically by name since distances are equal.

Constraints

  • 1 ≤ Users.id ≤ 500
  • 1 ≤ Users.name.length ≤ 30
  • 1 ≤ Rides.id ≤ 500
  • 1 ≤ Rides.distance ≤ 2000
  • Each user_id in Rides exists in Users table

Visualization

Tap to expand
Top Travellers: Users to Total DistanceInput: Users & Ridesidname1Alice2Bob3Alexuser_iddistance112023171220LEFT JOINGROUP BY + SUMOutput: Ranked by Distancenametravelled_distanceAlice340Bob317Alex0
Understanding the Visualization
1
Input Tables
Users and Rides tables
2
LEFT JOIN + GROUP BY
Connect and aggregate by user
3
Sorted Results
Ordered by distance DESC, name ASC
Key Takeaway
🎯 Key Insight: LEFT JOIN ensures all users appear in results, even those without rides

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
28.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen