Top Travellers

Top Travellers

What is this problem about?

The "Top Travellers coding problem" is an introductory SQL challenge that focuses on joining tables and performing aggregations. You are given two tables: one for Users (containing names and IDs) and one for Rides (containing user IDs and the distance traveled in each ride). The objective is to calculate the total distance traveled by each user and report the results ordered by distance (descending) and then by name (ascending). Importantly, users who have taken no rides should still appear in the list with a total distance of zero.

Why is this asked in interviews?

Companies like Google use this "Top Travellers interview question" to test a developer's understanding of different types of joins—specifically the LEFT JOIN. It also checks if the candidate knows how to handle NULL values that result from such joins. While simple, it reflects a very common real-world task: generating a summary report from relational data where some entities might have no activity.

Algorithmic pattern used

The "Database interview pattern" used here involves a LEFT JOIN between the Users table and the Rides table. This ensures that every user is included in the output. To handle users without rides, the COALESCE() or IFNULL() function is used to convert the resulting NULL distance into a 0. Finally, a GROUP BY clause on the user's name (or ID) and an ORDER BY clause are used to structure the final report.

Example explanation

Users Table: {1: Alice, 2: Bob, 3: Charlie} Rides Table: {User 1: 10km, User 1: 5km, User 2: 7km}

1. Join: Alice matches two rides, Bob matches one, Charlie matches none.
2. Aggregate: Alice's total is 10 + 5 = 15. Bob's total is 7. Charlie's total is NULL.
3. Clean: Convert Charlie's NULL to 0.
4. Sort: Alice (15), Bob (7), Charlie (0). The result clearly shows each user's total distance even if they haven't started traveling yet.

Common mistakes candidates make

The most frequent mistake is using an INNER JOIN instead of a LEFT JOIN. An inner join would completely exclude Charlie from the results, which violates the requirement to show all users. Another error is forgetting to use COALESCE(), leading to a result where some distances are displayed as empty or NULL instead of 0.

Interview preparation tip

When solving the "Top Travellers coding problem," always check if the problem asks for all entities or just the active ones. This tells you immediately whether you need an outer join. Practice using COALESCE to provide default values for your reports, as this is a standard professional practice.