Problem

Table: Users

1
2
3
4
5
6

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+

id is the primary key for this table. name is the name of the user.

Table: Rides

1
2
3
4
5
6
7

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| user_id       | int     |
| distance      | int     |
+---------------+---------+

id is the primary key for this table. user_id is the id of the user who traveled the distance “distance”.

Write an SQL query to report the distance traveled by each user.

Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order.

Solution

Method 1 - Right Join

Code

1
2
3
4
5

SELECT name, IFNULL(SUM(distance), 0) travelled_distance
FROM Rides r
RIGHT JOIN Users u ON u.id = r.user_id
GROUP by user_id
ORDER BY travelled_distance DESC, name;