[ 알고리즘 ] LeetCode 1651. Hopper Company Queries III

블로그를 이전 중이라 완료되기 전까지는 벨로그에 작성할 계획입니다.
이후 모든 글은 https://weekwith.me 에 작성 예정이니 다른 글이 궁금하시다면 해당 링크를 통해 방문해주세요.

본 글은 [ LeetCode ] 1651. Hopper Company Queries III를 풀고 작성한 글입니다.

문제

테이블

Table: Drivers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| driver_id   | int     |
| join_date   | date    |
+-------------+---------+
driver_id is the primary key for this table.
Each row of this table contains the driver's ID and the date they joined the Hopper company.

Table: Rides

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| ride_id      | int     |
| user_id      | int     |
| requested_at | date    |
+--------------+---------+
ride_id is the primary key for this table.
Each row of this table contains the ID of a ride, the user's ID that requested it, and the day they requested it.
There may be some ride requests in this table that were not accepted.

Table: AcceptedRides

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| ride_id       | int     |
| driver_id     | int     |
| ride_distance | int     |
| ride_duration | int     |
+---------------+---------+
ride_id is the primary key for this table.
Each row of this table contains some information about an accepted ride.
It is guaranteed that each accepted ride exists in the Rides table.

요구사항

Write an SQL query to compute the average_ride_distance and average_ride_duration of every 3-month window starting from January - March 2020 to October - December 2020. Round average_ride_distance and average_ride_duration to the nearest two decimal places.

The average_ride_distance is calculated by summing up the total ride_distance values from the three months and dividing it by 3 . The average_ride_duration is calculated in a similar way.

Return the result table ordered by month in ascending order, where month is the starting month's number (January is 1 , February is 2 , etc.).

풀이

접근법

이전 [ 알고리즘 ] LeetCode 1635. Hopper Company Queries I 문제 및 [ 알고리즘 ] LeetCode 1645. Hopper Company Queries II 동일하게 접근해서 풀면 된다.

WITH RECURSIVE 구를 활용하여 12개월 값을 전부 생성한 다음 각 월별 합계를 LEFT JOIN 구를 활용해 결합하고 이를 다시 3개월씩 끊어서 계산할 수 있게 JOIN 구를 사용하여 Months 테이블에 결합한다.

이때 유의해야 할 사항은 AVG 함수가 NULL 값을 0 으로 다루는 것이 아닌 아예 무시하기 때문에 기준이 되는 필드의 개수가 달라져 원하는 답이 나오지 않을 수 있다는 점이다.

나의 풀이

접근법을 토대로 푼 쿼리 결과는 아래와 같다.

WITH RECURSIVE Months (month) AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 AS month
    FROM Months
    WHERE month BETWEEN 1 AND 11
), MonthlyRides (month, monthly_distance, monthly_duration) AS (
    SELECT
        MONTH(Rides.requested_at) AS month,
        SUM(Acceptedrides.ride_distance) AS monthly_distance,
        SUM(Acceptedrides.ride_duration) AS monthly_duration
    FROM Rides
    JOIN AcceptedRides
    USING (ride_id)
    WHERE YEAR(Rides.requested_at) = 2020
    GROUP BY month
), MonthlyTotal (month, monthly_distance, monthly_duration) AS (
    SELECT
        Months.month,
        IFNULL(MonthlyRides.monthly_distance, 0) AS monthly_distance,
        IFNULL(MonthlyRides.monthly_duration, 0) AS monthly_duration
    FROM Months
    LEFT JOIN MonthlyRides
    USING (month)
), ThreeMonthlyAverage (month, average_ride_distance, average_ride_duration) AS (
    SELECT
        JanuaryToOctober.month,
        ROUND(AVG(MonthlyTotal.monthly_distance), 2) AS average_ride_distance,
        ROUND(AVG(MonthlyTotal.monthly_duration), 2) AS average_ride_duration
    FROM (
        SELECT month
        FROM Months
        WHERE month BETWEEN 1 AND 10
    ) AS JanuaryToOctober
    JOIN MonthlyTotal
    ON (MonthlyTotal.month - JanuaryToOctober.month) BETWEEN 0 AND 2
    GROUP BY JanuaryToOctober.month
)


SELECT
    month,
    average_ride_distance,
    average_ride_duration
FROM ThreeMonthlyAverage
ORDER BY month ASC;