Top Travellers - Problem
Top Travellers
You work for a ride-sharing company and need to generate a leaderboard showing which users have traveled the most distance. You have access to two database tables:
Users Table:
Rides Table:
Your task is to write an SQL query that calculates the total distance traveled by each user and returns the results sorted by:
1. Total distance in descending order (highest first)
2. User name in ascending order (alphabetical) for ties
The result should include all users, even those who haven't taken any rides (they should show 0 distance).
You work for a ride-sharing company and need to generate a leaderboard showing which users have traveled the most distance. You have access to two database tables:
Users Table:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+Rides Table:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| user_id | int |
| distance | int |
+---------------+---------+Your task is to write an SQL query that calculates the total distance traveled by each user and returns the results sorted by:
1. Total distance in descending order (highest first)
2. User name in ascending order (alphabetical) for ties
The result should include all users, even those who haven't taken any rides (they should show 0 distance).
Input & Output
example_1.sql — Basic Example
$
Input:
Users table:
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
+------+-----------+
Rides table:
+------+---------+----------+
| id | user_id | distance |
+------+---------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 3 | 30 |
+------+---------+----------+
›
Output:
+-----------+-------------------+
| name | travelled_distance|
+-----------+-------------------+
| Bob | 317 |
| Alex | 252 |
| Alice | 120 |
| Donald | 0 |
+-----------+-------------------+
💡 Note:
Bob has the highest distance (317), followed by Alex (222+30=252), Alice (120), and Donald who has no rides (0). Note that user_id 7 in rides doesn't exist in users table, so it's ignored.
example_2.sql — Tie Breaking
$
Input:
Users table:
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+------+-----------+
Rides table:
+------+---------+----------+
| id | user_id | distance |
+------+---------+----------+
| 1 | 1 | 100 |
| 2 | 2 | 100 |
+------+---------+----------+
›
Output:
+-----------+-------------------+
| name | travelled_distance|
+-----------+-------------------+
| Alice | 100 |
| Bob | 100 |
| Carol | 0 |
+-----------+-------------------+
💡 Note:
Alice and Bob both traveled 100 distance, so they're sorted alphabetically. Carol has no rides and appears last with 0 distance.
example_3.sql — No Rides Edge Case
$
Input:
Users table:
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
+------+-----------+
Rides table:
+------+---------+----------+
| id | user_id | distance |
+------+---------+----------+
(empty table)
›
Output:
+-----------+-------------------+
| name | travelled_distance|
+-----------+-------------------+
| Alice | 0 |
| Bob | 0 |
+-----------+-------------------+
💡 Note:
When no rides exist, all users should still appear in the result with 0 distance, sorted alphabetically by name.
Visualization
Tap to expand
Understanding the Visualization
1
Member Database
Start with all registered members (Users table)
2
Trip Records
Have separate records of all trips taken (Rides table)
3
Smart Matching
Use LEFT JOIN to match members with their trips (keeps members with no trips)
4
Calculate Totals
Sum up distances for each member, using 0 for those with no trips
5
Create Leaderboard
Sort by total distance (highest first), break ties alphabetically
Key Takeaway
🎯 Key Insight: LEFT JOIN is crucial to include all users, while GROUP BY with COALESCE elegantly handles aggregation and null values in one efficient query.
Time & Space Complexity
Time Complexity
O(n + m + k log k)
O(n + m) for JOIN where n=users, m=rides, plus O(k log k) for sorting k results
⚡ Linearithmic
Space Complexity
O(k)
Space for k result rows where k is number of users
✓ Linear Space
Constraints
- 1 ≤ Users.id ≤ 500
- 1 ≤ Users.name.length ≤ 30
- 1 ≤ Rides.id ≤ 500
- 1 ≤ Rides.user_id ≤ 500
- 1 ≤ Rides.distance ≤ 1000
- All user names are unique
- Some users may have no rides
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code