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 ProblemInput TablesUsers1 | Alice2 | Bob3 | CharlieRides1 | 1 | 1202 | 2 | 3173 | 1 | 222LEFTJOINGROUPSUMOutputResult1 | Alice | 3422 | Bob | 3173 | Charlie | 0↑ No rides = 0
Understanding the Visualization
1
LEFT JOIN
Connect Users with Rides (keep all users)
2
GROUP BY
Group by user to aggregate rides
3
SUM + COALESCE
Calculate total distance, 0 for no rides
Key Takeaway
🎯 Key Insight: LEFT JOIN is essential when you need to include all records from one table, even when there are no matching records in the joined table

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