[TIL178] leetcode - Trips and Users

문제 설명

이번 문제는 2013년 10월 1일부터 10월 3일까지의 택시 운행 데이터를 대상으로, 차단되지 않은 고객과 차단되지 않은 운전자 간의 요청만 고려하여 일별 취소율(Cancellation Rate)을 계산하는 문제였다.

취소율은 다음과 같이 정의된다.

취소율
=
취소된 요청 수
/
전체 요청 수

여기서 취소된 요청은

cancelled_by_driver
cancelled_by_client

상태를 의미하며,

completed

상태는 취소가 아닌 정상 완료 요청이다.

또한 문제에서 가장 중요한 조건은 다음이었다.

고객(Client)과 운전자(Driver)가 모두 차단되지 않은 사용자여야 한다.

---

내가 처음 생각한 접근

처음에는 Trips 테이블과 Users 테이블을 조인한 후,

JOIN users u
ON u.users_id = t.client_id
OR u.users_id = t.driver_id

방식으로 연결했다.

그리고

WHERE banned = 'No'

조건을 주면 차단되지 않은 사용자만 남을 것이라고 생각했다.

취소 건수는

COUNT(
    CASE
        WHEN status <> 'completed'
        THEN id
        ELSE 0
    END
)

으로 계산하려고 했다.

방향 자체는 맞았다.

1. 차단 사용자 제거
2. 취소 건수 계산
3. 전체 건수로 나누기

라는 흐름은 문제 의도와 일치했다.

하지만 구현 과정에서 중요한 SQL 개념을 놓치고 있었다.

---

내가 헷갈렸던 부분

1. Users를 한 번만 JOIN하면 되는 줄 알았다

처음에는

JOIN users u
ON u.users_id = t.client_id
OR u.users_id = t.driver_id

를 사용했다.

하지만 이 방식은 한 운행 건이 여러 행으로 늘어나는 문제를 만든다.

예를 들어

Trips

idclient_iddriver_id

1

1

10

Users

users_idbanned

1	No
10	No

이라면

JOIN 결과는

trip1 - user1
trip1 - user10

두 행이 생성된다.

즉 원래 1건이었던 운행이 2건으로 집계된다.

결국 분모와 분자가 모두 왜곡된다.

---

2. 고객과 운전자 모두 확인해야 한다는 점

문제를 다시 읽어보니

차단되지 않은 고객
AND
차단되지 않은 운전자

를 의미하고 있었다.

그런데 나는

WHERE banned = 'No'

만 사용했다.

이렇게 되면

고객 : No
운전자 : Yes

인 경우도 일부 결과에 포함될 수 있다.

즉 문제 조건을 정확히 만족하지 못한다.

---

질문하며 이해한 내용

질문

왜 Users를 두 번 JOIN해야 하지?

→ 답변

Trips 테이블에는 사용자 FK가 두 개 존재한다.

client_id
driver_id

각각 Users 테이블을 참조한다.

따라서 고객 정보와 운전자 정보를 각각 가져오기 위해 두 번 JOIN해야 한다.

→ 내가 이해한 내용

FK가 두 개면
같은 테이블을 두 번 JOIN할 수 있다.

---

질문

COUNT() 안에 ELSE 0을 넣으면 왜 안 되지?

→ 답변

COUNT()는 NULL이 아닌 값을 모두 센다.

1
0
-1
100

모두 카운트된다.

→ 내가 이해한 내용

조건부 집계에서는

COUNT(
    CASE
        WHEN 조건
        THEN 1
    END
)

또는

SUM(
    CASE
        WHEN 조건
        THEN 1
        ELSE 0
    END
)

패턴을 사용해야 한다.

---

질문

왜 AVG(status != 'completed')가 가능하지?

→ 답변

MySQL은

status != 'completed'

를 평가하면

TRUE → 1
FALSE → 0

으로 변환한다.

따라서

AVG(status != 'completed')

는

취소 건수
/
전체 건수

와 동일하다.

→ 내가 이해한 내용

비율 계산 문제에서는 AVG()도 좋은 선택지가 될 수 있다.

---

최종 해결 방법

이 문제를 해결하기 위한 흐름은 다음과 같다.

Trips 조회
↓
고객 Users JOIN
↓
운전자 Users JOIN
↓
둘 다 banned = 'No' 필터
↓
날짜 범위 필터
↓
취소 건수 계산
↓
전체 건수 계산
↓
비율 계산
↓
ROUND( , 2)

---

내가 작성한 쿼리

select request_at as 'Day', 
COUNT(case when status <> 'completed' THEN id ELSE 0 END) * 100 / COUNT(*) as 'Cancellation Rate'
from trips t JOIN users u ON u.users_id = t.client_id OR u.users_id = t.driver_id
where banned = 'No' 
AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
group by request_at

---

정답 쿼리

SELECT
    request_at AS Day,
    ROUND(
        SUM(
            CASE
                WHEN status != 'completed' THEN 1
                ELSE 0
            END
        ) / COUNT(*),
        2
    ) AS "Cancellation Rate"
FROM Trips t
JOIN Users c
    ON t.client_id = c.users_id
JOIN Users d
    ON t.driver_id = d.users_id
WHERE c.banned = 'No'
    AND d.banned = 'No'
    AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at;

---

오답 및 시행착오

오답 1

JOIN users u
ON u.users_id = t.client_id
OR u.users_id = t.driver_id

문제점

한 운행 건이 여러 행으로 증가

---

오답 2

WHERE banned = 'No'

문제점

고객과 운전자를 각각 검증하지 못함

---

오답 3

COUNT(
CASE
WHEN status <> 'completed'
THEN id
ELSE 0
END
)

문제점

0도 COUNT 대상

---

정답 풀이 해설

이 문제를 처음 봤다면 떠올려야 하는 사고 흐름

취소율 계산
↓
분자와 분모 정의
↓
어떤 요청만 포함하는가?
↓
차단되지 않은 고객
+
차단되지 않은 운전자
↓
Users가 두 역할로 등장
↓
Users 2번 JOIN
↓
조건부 집계
↓
비율 계산

---

이번 문제에서 배운 패턴

한 테이블에
동일 참조 FK 여러 개 존재
↓
같은 마스터 테이블 여러 번 JOIN
↓
역할별 조건 적용
↓
조건부 집계
↓
비율 계산

---

비슷한 문제가 나오면 어떻게 접근할까?

먼저 생각할 것

비율인가?
↓
분자 정의
↓
분모 정의
↓
어떤 데이터만 포함하는가?
↓
JOIN 필요?
↓
조건부 집계

특히

고객
운전자

작성자
수정자

판매자
구매자

처럼 하나의 테이블을 여러 역할로 참조하는 경우에는

같은 테이블을 여러 번 JOIN

해야 한다는 점을 기억하자.

---

오늘 배운 점

COUNT()는 숫자를 세는 함수가 아니라

NULL이 아닌 값을 세는 함수

라는 점을 확실히 이해하게 되었다.

또한 하나의 테이블을 여러 역할로 참조할 때는 별칭(alias)을 사용하여 여러 번 JOIN할 수 있다는 점도 배웠다.

---

다음에 같은 문제를 만나면

1. 비율인지 확인
2. 분자 정의
3. 분모 정의
4. 역할별 JOIN 여부 확인
5. COUNT와 SUM 사용 방식 점검

순서로 접근할 것이다.

---

핵심 한 줄 요약

"한 행에 여러 사용자 역할(FK)이 존재하면 같은 테이블을 역할별로 JOIN하고, 조건부 집계에서는 COUNT의 NULL 동작을 반드시 고려해야 한다."