Total Traveled Distance - Solution & Explanation

Problem Overview: You are given two tables: Users and Rides. Each ride stores the distance traveled by a user. The task is to compute the total traveled distance for every user, including users who never took a ride. The result must show each user's id, name, and the summed distance.

Approach 1: LEFT JOIN + GROUP BY SUM (O(U + R) time, O(1) extra space)

The core idea is to join the Users table with the Rides table and aggregate ride distances per user. Use a LEFT JOIN so every user appears in the result even if they have no rides. Then apply GROUP BY user_id and compute SUM(distance) to accumulate all rides for that user. When a user has no matching rides, SUM() returns NULL, so wrap it with COALESCE(..., 0) to output 0.

This approach works because relational databases efficiently process joins and aggregations during a single scan of the joined dataset. The database groups rows by user and calculates the sum during aggregation. Time complexity is O(U + R) where U is the number of users and R is the number of rides, since the engine scans both tables once during the join. Space complexity is O(1) outside the result set because aggregation happens internally.

This pattern appears frequently in SQL interview questions: join a dimension table with an activity table and compute totals per entity. Understanding how LEFT JOIN preserves unmatched rows is critical. Problems involving user activity, purchases, or metrics often rely on the same technique using SQL aggregation and joins.

Approach 2: Correlated Subquery Aggregation (O(U * R) worst case, O(1) space)

Another option is computing the total distance with a correlated subquery for each user. For every row in Users, run a subquery that calculates SUM(distance) from Rides filtered by user_id. This avoids an explicit join but repeatedly scans the rides table. In the worst case, the database performs the aggregation once per user, resulting in O(U * R) time.

While simpler to read, this approach performs worse on large datasets because the rides table may be scanned many times. Modern query optimizers sometimes rewrite it into a join internally, but relying on that behavior is not ideal.

Recommended for interviews: The LEFT JOIN + GROUP BY solution is the expected answer. It demonstrates a clear understanding of relational joins, aggregation, and handling missing rows with COALESCE. Interviewers want to see that you recognize the pattern of joining activity data and summarizing it using database aggregation.