Total Traveled Distance - Problem

You are given two tables: Users and Rides. Write a SQL solution to calculate the total distance traveled by each user.

Key Requirements:

  • Include all users even if they haven't completed any rides
  • Users with no rides should show distance as 0
  • Return user_id, name, and total traveled distance
  • Order results by user_id in ascending order

Table Schema

Users
Column Name Type Description
user_id PK int Unique identifier for each user
name varchar Name of the user
Primary Key: user_id
Rides
Column Name Type Description
ride_id PK int Unique identifier for each ride
user_id int Foreign key referencing Users table
distance int Distance traveled in the ride
Primary Key: ride_id

Input & Output

Example 1 — Standard Case with Mixed Activity
Input Tables:
Users
user_id name
1 Alice
2 Bob
3 Charlie
Rides
ride_id user_id distance
1 1 120
2 2 317
3 1 222
Output:
user_id name traveled_distance
1 Alice 342
2 Bob 317
3 Charlie 0
💡 Note:

Alice has two rides (120 + 222 = 342), Bob has one ride (317), and Charlie has no rides so shows 0. The LEFT JOIN ensures Charlie appears in results even without rides.
Example 2 — All Users Have No Rides
Input Tables:
Users
user_id name
1 Alice
2 Bob
Rides
ride_id user_id distance
Output:
user_id name traveled_distance
1 Alice 0
2 Bob 0
💡 Note:

When the Rides table is empty, all users show 0 distance. COALESCE converts NULL SUM results to 0.

Constraints

  • 1 ≤ user_id ≤ 300
  • 1 ≤ ride_id ≤ 500
  • 1 ≤ distance ≤ 999
  • name consists of lowercase English letters only

Visualization

Tap to expand
Total Traveled Distance INPUT Users Table user_id name 1 Alice 2 Bob 3 Charlie Rides Table ride_id user_id distance 1 1 120 2 1 80 3 2 50 4 2 30 Note: User 3 (Charlie) has NO rides in the Rides table ALGORITHM STEPS 1 LEFT JOIN Users LEFT JOIN Rides on user_id to keep all users 2 GROUP BY Group results by user_id and name 3 SUM with COALESCE COALESCE(SUM(distance), 0) Convert NULL to 0 4 ORDER BY Sort by user_id ASC SELECT u.user_id, u.name, COALESCE(SUM(r.distance),0) FROM Users u LEFT JOIN Rides r GROUP BY u.user_id ORDER BY 1 FINAL RESULT user_id name total 1 Alice 200 2 Bob 80 3 Charlie 0 Calculations: Alice: 120 + 80 = 200 Bob: 50 + 30 = 80 Charlie: NULL --> 0 (no rides) LEFT JOIN ensures: U Users + R Rides = OK Key Insight: LEFT JOIN preserves all users from the Users table, even those without matching rides. COALESCE handles NULL values (users with no rides) by converting them to 0. This combination ensures every user appears in the result with their correct total distance. TutorialsPoint - Total Traveled Distance | Optimal Solution (LEFT JOIN + COALESCE + GROUP BY)

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
25.4K Views
High Frequency
~8 min Avg. Time
890 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