[LeetCode] 262. Trips and Users

🔗 문제 링크

https://leetcode.com/problems/trips-and-users/description/

 

 

🔓 문제 풀이

풀이1>

SELECT 
    t.request_at AS 'Day',
    ROUND(
        SUM(
            CASE 
                WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') 
                     AND u1.banned = 'No'
                     AND u2.banned = 'No' 
                THEN 1 
                ELSE 0 
            END
        ) / 
        NULLIF(
            SUM(
                CASE 
                    WHEN u1.banned = 'No' 
                         AND u2.banned = 'No' 
                    THEN 1 
                    ELSE 0 
                END
            ), 0
        ), 2) AS 'Cancellation Rate'
FROM trips t
JOIN users u1 ON t.client_id = u1.users_id
JOIN users u2 ON t.driver_id = u2.users_id
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at
HAVING COUNT(t.request_at) > 0
   AND ROUND(
        SUM(
            CASE 
                WHEN t.status IN ('cancelled_by_driver', 'cancelled_by_client') 
                     AND u1.banned = 'No'
                     AND u2.banned = 'No' 
                THEN 1 
                ELSE 0 
            END
        ) / 
        NULLIF(
            SUM(
                CASE 
                    WHEN u1.banned = 'No' 
                         AND u2.banned = 'No' 
                    THEN 1 
                    ELSE 0 
                END
            ), 0
        ), 2) IS NOT NULL
ORDER BY t.request_at;

 

 

취소율을 구하는 문제이다.
취소된 데이터 = (status가 'cancelled_by_driver'이거나 'cancelled_by_client') + 정지되지 않은 유저(banned='No')
전체 데이터 = 정지되지 않은 유저

즉, 취소된 데이터의 행 수 / 전체 데이터 수로 구하면 된다.
case문으로 각 조건을 구하고 부합하면 1 아니면 0으로 하고 SUM 함수를 이용해 조건에 부합하는 행의 수를 구했다.
또 분모가 0이 되는 상황을 피하기 위해 nullif 함수를 이용해서 0이면 NULL로 했고, having 조건에 분모가 NULL인 경우를 제외했다. 

정답은 맞았지만, 의식의 흐름대로 쿼리를 작성해서 중복되는 조건이 많아 맘에들지 않았다.

 

풀이2>

SELECT 
    t.request_at AS 'Day',
    ROUND(
        SUM(
            CASE 
                WHEN t.status <> 'completed'
                THEN 1 
                ELSE 0 
            END
        ) / 
        count(*), 2) AS 'Cancellation Rate'
FROM trips t
JOIN users u1 ON t.client_id = u1.users_id
JOIN users u2 ON t.driver_id = u2.users_id
WHERE t.request_at >= '2013-10-01' and t.request_at <='2013-10-03'
and (u1.banned = 'No'
     AND u2.banned ='No')
GROUP BY t.request_at;

수정한 쿼리다.
위와 로직은 비슷하지만 달라진 게 있다면,
select문에서 선언한 중복된 코드(정지되지 않은 유저(banned='No'),공통적 로직)을 WHERE절로 보냈다.
그리고 전체 데이터 행수를 구할 때 NULLIF(SUM(~)) 조건을 COUNT(*)로 바꾸었다. 

✅ 사용 문법 정리

NULLIF(expression1, expression2)
: 두 개의 인자가 같으면 NULL을 반환하고, 다르면 첫 번째 인자(expression1)를 반환

ROUND(number, decimals)
: 숫자 number를 소수점 이하 decimals 자릿수로 반올림

 

📑 참고하면 좋은 자료

https://community.heartcount.io/ko/query-optimization-tips/

SQL 쿼리 성능 최적화를 위한 튜닝 팁 6가지 (Query Optimization)

쿼리 성능 향상 방법에 대해 잘 정리된 문서이다.
여러가지 방법을 소개하는데 읽어보고 싧습해보면 도움이 될 것 같다!