1407. Top Travellers
Problem Statement
Report the distance travelled by each user, ordered by distance descending; on a tie, order alphabetically by name.
Table Schema
Table: Users
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key.
Table: Rides
+-------------+------+
| id | int |
| user_id | int |
| distance | int |
+-------------+------+
id is the primary key.
Examples
Example 1
Input Table:
Users table:
+----+--------+
| id | name |
+----+--------+
| 1 | Alice |
| 2 | Bob |
+----+--------+
Rides table:
+----+---------+----------+
| id | user_id | distance |
+----+---------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 2 | 22 |
+----+---------+----------+
Expected Output:
+-------+---------------------+
| name | travelled_distance |
+-------+---------------------+
| Bob | 339 |
| Alice | 120 |
+-------+---------------------+
SQL Solution
SELECT u.name, COALESCE(SUM(r.distance), 0) AS travelled_distance
FROM Users u
LEFT JOIN Rides r
ON u.id = r.user_id
GROUP BY u.id, u.name
ORDER BY travelled_distance DESC, u.name ASC;
Problem Info
DifficultyEASY
Topics
joinleft-joingroup-byorder-by
Reference Links