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 - SQL Query Solution INPUT TABLES Users Table id name 1 Alice 2 Bob 3 Charlie Rides Table id user_id distance 1 1 120 2 1 80 3 2 150 Users.id = Rides.user_id (Charlie has no rides) ALGORITHM STEPS 1 LEFT JOIN Join Users with Rides on Users.id = Rides.user_id 2 SUM with IFNULL Calculate total distance IFNULL(SUM(distance),0) 3 GROUP BY Group results by user GROUP BY Users.id 4 ORDER BY Sort by distance DESC, then name ASC SELECT name, IFNULL(SUM(distance),0) FROM Users LEFT JOIN Rides GROUP BY id ORDER BY... FINAL RESULT name travelled Alice 200 Bob 150 Charlie 0 Ordering Logic: 1. Alice: 120+80 = 200 2. Bob: 150 3. Charlie: 0 (no rides) OK Query Complete Key Insight: LEFT JOIN ensures ALL users appear in results, even those with no rides. IFNULL/COALESCE handles NULL values from the join by converting them to 0. The dual ORDER BY sorts by distance first (descending), then alphabetically by name (ascending) for users with the same total distance. TutorialsPoint - Top Travellers | Optimal Solution

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