Top Travellers
Lyft
Problem Statement
Lyft is running a rewards program and needs a leaderboard of its users.
Write an SQL query to report the distance traveled by each user.
Rules:
- Return the name and the total traveled_distance.
- If a user has not traveled any distance, you must report it as 0.
- Results must be sorted by traveled_distance in descending order.
- If two users have the same traveled_distance, sort them by name in ascending order.
Tests your understanding of
Basic SQL, JOIN, Aggregation and COALESCE
Input Tables
users
| id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
| 7 | Eve |
| 13 | Jonathan |
| 19 | Elvis |
rides
| id(INTEGER) | user_id(INTEGER) | distance(INTEGER) |
|---|---|---|
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
Expected Output
| name(VARCHAR) | traveled_distance(INTEGER) |
|---|---|
| Elvis | 450 |
| Eve | 450 |
| Bob | 317 |
| Jonathan | 312 |
| Charlie | 222 |
| Alice | 120 |
| David | 0 |
Tags
EasyBasic SQLJOINAggregationCOALESCE
10-15 min
68%