SQL문제풀기

[SQL 문제 풀이] Top Travellers (최고의 여행자)

Jun 18, 2025

[SQL 문제 풀이] Top Travellers (최고의 여행자)

Contents

내가 작성한 쿼리

Top Travellers - LeetCode

Can you solve this real interview question? Top Travellers - Table: Users +---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ id is the column with unique values 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 column with unique values for this table. user_id is the id of the user who traveled the distance "distance". Write a solution to report the distance traveled by each user. Return the result table ordered by travelled_distance in descending order, if two or more users traveled the same distance, order them by their name in ascending order. The result format is in the following example. Example 1: Input: 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 | +------+----------+----------+ Output: +----------+--------------------+ | name | travelled_distance | +----------+--------------------+ | Elvis | 450 | | Lee | 450 | | Bob | 317 | | Jonathan | 312 | | Alex | 222 | | Alice | 120 | | Donald | 0 | +----------+--------------------+ Explanation: Elvis and Lee traveled 450 miles, Elvis is the top traveler 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 did not have any rides, the distance traveled by him is 0.

https://leetcode.com/problems/top-travellers/description/

Top Travellers - LeetCode

notion image


각 사용자가 이동한 거리를 보고하는 솔루션을 작성하세요.

두 명 이상의 name가 같은 거리를 이동한 경우,
travelled_distance로 정렬된 결과 테이블을 내림차순으로 반환합니다.

내가 작성한 쿼리

Oracle


SELECT
    u.name,
    SUM(NVL(distance, 0)) AS travelled_distance
FROM Users u
LEFT OUTER JOIN Rides r
    ON u.id = r.user_id
GROUP BY u.id, u.name
ORDER BY 
    travelled_distance DESC,
    u.name;

MySQL


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

차이점

NULL처리 함수 명의 차

Share article

More articles

[JAVA 문제 풀이] 329. 신고 결과 받기

[JAVA 문제 풀이] 329. 신고 결과 받기

[JAVA 문제 풀이] 328. 동영상 재생기

[JAVA 문제 풀이] 328. 동영상 재생기

[SQL 문제 풀이] Calculate Special Bonus (특별 보너스 계산)

[SQL 문제 풀이] Calculate Special Bonus (특별 보너스 계산)

[JAVA 문제 풀이] 327. 게임 맵 최단 거리

[JAVA 문제 풀이] 327. 게임 맵 최단 거리

stupefyee

RSS·Powered by Inblog