SQL problem - Top Travellers

Problem

Query

SELECT
    u.name,
    IF(r.distance IS NOT NULL, SUM(r.distance), 0) AS travelled_distance
FROM
    Users as u
LEFT JOIN
    Rides as r
    ON u.id = r.user_id
GROUP BY u.id
ORDER BY 
    travelled_distance DESC,
    u.name ASC

Editorial Solution

• Since the question is asking for the distance travelled by each user and there may be users who have not travelled any distance, LEFT JOIN is needed so each user from the Users table will be included.
• For those users who have not travelled, functions such as IFNULL() or COALESCE() are needed to return 0 instead of null for their total distance. The two functions are a little bit different, but for this question, they can be used interchangeably.
  • IFNULL(): takes two arguments and returns the first one if it’s not NULL or the second if the first one is NULL.
  • COALESCE(): takes two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are NULL.
• Since users might have the same name and id is the primary key for this table (which means the values in this column will be unique). We need to use id for GROUP BY to get the aggregated distance for each user.

SELECT 
    u.name, 
    IFNULL(SUM(distance),0) AS travelled_distance
FROM 
    Users u
LEFT JOIN 
    Rides r
ON 
    u.id = r.user_id
GROUP BY 
    u.id
ORDER BY 2 DESC, 1 ASC