Leetcode: Top Travellers

 The question is asked at Point72. It wants SQL query to report the distance travelled by each user. The query format should have the user name and travelled distance. 

Here are the table formats: 

We select user id, name and the total sum of the travelled distance. We want to join by the id name and we want to group by the user id. Here is a rough draft:

SELECT user_id, name, SUM distance AS travelled_distance

FROM Users JOIN Rides ON Users.id = Rides.user_id

GROUP BY user_id

We don't want to select USER_ID, so we can attempt to get rid of that. 

SELECT name, SUM distance AS travelled_distance

FROM Users JOIN Rides ON Users.id = Rides.user_id

GROUP BY user_id

COALESCE replaces NULL values with an integer or something else. 

In ORDER BY 2 DESC, 2 is the second column and DESC is descending. 1 ASC orders by names afterwards. Here's the final query. 

SELECT name, COALESCE(SUM(distance, 0) AS travelled_distance

FROM Users LEFT JOIN Rides ON Users.id = Rides.user_id

GROUPT BY user_id

ORDER BY 2 DESC, 1 ASC 

 

and actual query: 

SELECT name, COALESCE(SUM(distance),0) AS travelled_distance

FROM Users LEFT JOIN Rides ON Users.id = Rides.user_id

GROUP BY user_id

ORDER BY 2 DESC, 1 ASC;