forked from fishercoder1534/Leetcode
-
Notifications
You must be signed in to change notification settings - Fork 11
Expand file tree
/
Copy path_1407.sql
More file actions
86 lines (85 loc) · 2.6 KB
/
_1407.sql
File metadata and controls
86 lines (85 loc) · 2.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
--1407. Top Travellers
--
--Table: Users
--
--+---------------+---------+
--| Column Name | Type |
--+---------------+---------+
--| id | int |
--| name | varchar |
--+---------------+---------+
--id is the primary key for this table.
--name is the name of the user.
--
--
--Table: Rides
--
--+---------------+---------+
--| Column Name | Type |
--+---------------+---------+
--| id | int |
--| user_id | int |
--| distance | int |
--+---------------+---------+
--id is the primary key for this table.
--city_id is the id of the city who bought the product "product_name".
--
--
--Write an SQL query to report the distance travelled by each user.
--
--Return the result table ordered by travelled_distance in descending order, if two or more users travelled the same distance, order them by their name in ascending order.
--
--The query result format is in the following example.
--
--Users table:
--+------+-----------+
--| id | name |
--+------+-----------+
--| 1 | Alice |
--| 2 | Bob |
--| 3 | Alex |
--| 4 | Donald |
--| 7 | Lee |
--| 13 | Jonathan |
--| 19 | Elvis |
--+------+-----------+
--
--Rides table:
--+------+----------+----------+
--| id | user_id | distance |
--+------+----------+----------+
--| 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 |
--+------+----------+----------+
--
--Result table:
--+----------+--------------------+
--| name | travelled_distance |
--+----------+--------------------+
--| Elvis | 450 |
--| Lee | 450 |
--| Bob | 317 |
--| Jonathan | 312 |
--| Alex | 222 |
--| Alice | 120 |
--| Donald | 0 |
--+----------+--------------------+
--Elvis and Lee travelled 450 miles, Elvis is the top traveller as his name is alphabetically smaller than Lee.
--Bob, Jonathan, Alex and Alice have only one ride and we just order them by the total distances of the ride.
--Donald didn't have any rides, the distance travelled by him is 0.
--
--# Write your MySQL query statement below
--credit: https://leetcode.com/problems/top-travellers/discuss/572803/MySQL-Simple-Solution
select name, sum(ifnull(distance, 0)) as travelled_distance
from rides r
right join users u
on r.user_id = u.id
group by name
order by 2 desc,1 asc;