문제
Table: Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | varchar |
+-------------+----------+
id is the primary key (column with unique values) for this table.
The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are foreign keys to the users_id at the Users table.
Status is an ENUM (category) type of ('completed', 'cancelled_by_driver', 'cancelled_by_client').
Table: Users
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id is the primary key (column with unique values) for this table.
The table holds all users. Each user has a unique users_id, and role is an ENUM type of ('client', 'driver', 'partner').
banned is an ENUM (category) type of ('Yes', 'No').
The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.
Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03" with at least one trip. Round Cancellation Rate to two decimal points.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Trips table:
+----+-----------+-----------+---------+---------------------+------------+
| id | client_id | driver_id | city_id | status | request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+
Users table:
+----------+--------+--------+
| users_id | banned | role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
Output:
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
Explanation:
On 2013-10-01:
- There were 4 requests in total, 2 of which were canceled.
- However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation.
- Hence there are 3 unbanned requests in total, 1 of which was canceled.
- The Cancellation Rate is (1 / 3) = 0.33
On 2013-10-02:
- There were 3 requests in total, 0 of which were canceled.
- The request with Id=6 was made by a banned client, so it is ignored.
- Hence there are 2 unbanned requests in total, 0 of which were canceled.
- The Cancellation Rate is (0 / 2) = 0.00
On 2013-10-03:
- There were 3 requests in total, 1 of which was canceled.
- The request with Id=8 was made by a banned client, so it is ignored.
- Hence there are 2 unbanned request in total, 1 of which were canceled.
- The Cancellation Rate is (1 / 2) = 0.50내 풀이
WITH
clean_uids AS (
SELECT users_id
FROM Users
WHERE banned = 'No'
),
joined AS (
SELECT request_at,
(CASE
WHEN status = 'completed' THEN 0
ELSE 1
END) AS status
FROM Trips t
JOIN clean_uids c1 ON t.driver_id = c1.users_id
JOIN clean_uids c2 ON t.client_id = c2.users_id
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
)
SELECT request_at AS "Day",
ROUND(SUM(status) / COUNT(status), 2) AS "Cancellation Rate"
FROM joined
GROUP BY request_at;내 풀이의 특징 및 단점
- 특징:
clean_uids라는 CTE를 사용하여banned = 'No'인 사용자만 필터링한 후, 이를Trips테이블과 두 번 조인하여 클라이언트와 드라이버가 모두 차단되지 않은 요청만 추출한다. 이후status를 기반으로 취소 여부를 0 또는 1로 변환하여 취소율을 계산한다. - 단점:
Users테이블을 두 번 조인하기 위해clean_uids를 두 번 참조한다. 이는Users테이블을 두 번 스캔하는 효과를 내며, 데이터가 클 경우 메모리 사용량이 증가한다.- CTE는 중간 결과를 물리적으로 저장(materialize)할 가능성이 있어 성능 저하를 유발할 수 있다.
CASE WHEN구문은 직관적이지만, MySQL에서IF와 같은 더 간결한 함수를 사용할 수 있음에도 불구하고 사용하지 않았다.
다른 사람의 풀이
SELECT request_at AS Day,
ROUND(COALESCE(SUM(status != 'completed') / COUNT(*), 0), 2) AS 'Cancellation Rate'
FROM Trips t
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND NOT EXISTS (
SELECT 1
FROM Users u
WHERE banned = 'Yes'
AND (t.client_id = u.users_id OR t.driver_id = u.users_id)
)
GROUP BY request_at;다른 사람의 풀이의 특징 및 단점
- 특징:
NOT EXISTS를 사용하여 차단된 사용자를 필터링한다. 이는 물리적 조인 없이 조건을 처리하므로 조인 비용을 줄일 수 있다.SUM(status != 'completed')를 사용하여 취소된 요청을 직접 계산하며,COALESCE로 0 나누기 문제를 방지한다.Users테이블에 대한 단일 서브쿼리로 조건을 처리하여 두 번 조인하는 것보다 효율적이다.
- 단점:
NOT EXISTS내부의OR조건은 MySQL 옵티마이저가 최적화하지 못할 가능성이 있다. 특히Users테이블이 크고 인덱스가 적절히 설정되지 않은 경우, 서브쿼리가 반복 실행되어 성능이 저하될 수 있다.NOT EXISTS는 인덱스 활용에 따라 성능이 달라질 수 있으며, 인덱스가 없으면 전체 테이블 스캔을 유발할 수 있다.
비교 결론
- 대규모 데이터 환경에서는
NOT EXISTS를 사용한 다른 사람의 풀이가 조인 횟수가 적어 성능상 유리할 가능성이 높다. 하지만Users테이블의 크기와 인덱스 설정에 따라 성능이 달라질 수 있으므로, 실제 실행 계획을 확인하는 것이 중요하다.
쿼리 최적화 분석
성능 개선 방식 1: 조인 최적화
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 d ON t.driver_id = d.users_id AND d.banned = 'No'
JOIN Users c ON t.client_id = c.users_id AND d.banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at;개선된 점
- 중간 테이블 제거:
clean_uidsCTE를 제거하고Users테이블을 직접 조인하여 중간 결과 저장 비용을 줄였다. - 조건 통합:
banned = 'No'조건을 조인 시점에 바로 적용하여 필터링을 조기에 수행한다. 이는 SQL 옵티마이저가 더 효율적인 실행 계획을 선택할 가능성을 높인다. - 인덱스 활용 가능성:
Users.users_id와Users.banned에 인덱스가 있다면, 조인과 필터링이 효율적으로 수행된다. - 드라이빙 테이블 최적화:
Trips테이블을 드라이빙 테이블로 설정하고,Users테이블을 두 번 조회한다.Trips테이블이 크고Users테이블이 상대적으로 작을 경우, 이는 효율적이다.
적용 시나리오
Trips테이블이 수천만 건 이상이고,Users테이블이 상대적으로 작으며,Users.users_id와Users.banned에 인덱스가 있는 경우 적합하다.- SQL 옵티마이저가 조인 순서를 최적화하여
Trips를 먼저 읽고 필요한Users데이터만 조회하도록 유도한다.
성능 개선 방식 2: IN 서브쿼리 기반
WITH filtered AS (
SELECT request_at,
IF(status = 'completed', 0, 1) AS status
FROM Trips t
WHERE t.driver_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.client_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
)
SELECT request_at AS "Day",
ROUND(SUM(status) / COUNT(*), 2) AS "Cancellation Rate"
FROM filtered
GROUP BY request_at;개선된 점
- 중간 테이블 간소화:
clean_uids를 제거하고IN서브쿼리로 직접 필터링하여 중간 결과의 저장 비용을 줄였다. - MySQL 최적화:
IF(status = 'completed', 0, 1)을 사용하여CASE WHEN보다 간결하고 빠르게 처리될 가능성이 있다. - 인덱스 활용:
Users.users_id와Users.banned에 인덱스가 있다면,IN서브쿼리가 효율적으로 실행된다. - 드라이빙 테이블 명확화:
Trips를 드라이빙 테이블로 설정하여 대규모 데이터 처리에 적합하다.
적용 시나리오
Users테이블이 작고,users_id또는banned컬럼에 인덱스가 설정되어 있는 경우 효율적이다.Trips테이블이 크고,IN서브쿼리가 인덱스를 활용하여 빠르게 필터링할 수 있는 환경에 적합하다.
성능 비교를 위한 팁
인덱스 확인 및 실행 계획 분석
실행 계획 확인
MySQL에서 EXPLAIN 또는 EXPLAIN ANALYZE를 사용하여 쿼리의 실행 계획을 확인할 수 있다. 이는 쿼리가 어떻게 실행되는지, 어떤 인덱스를 사용하는지, 어떤 테이블이 먼저 읽히는지 등을 알 수 있게 해준다.
EXPLAIN
SELECT request_at,
IF(status = 'completed', 0, 1) AS status
FROM Trips t
WHERE t.driver_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.client_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03';실행 계획의 주요 확인 항목:
- 드라이빙 테이블: 어떤 테이블이 먼저 읽히는지 확인한다. 일반적으로
Trips가 드라이빙 테이블이 되어야 효율적이다. - 조인 방식:
Nested Loop,Hash Join,Merge Join중 어떤 방식이 사용되는지 확인한다.Nested Loop은 인덱스가 잘 활용될 때 효율적이다. - 인덱스 사용 여부:
key와possible_keys컬럼을 통해 어떤 인덱스가 사용되는지 확인한다. - 필터링 위치:
Extra컬럼에Using where나Using index가 나타나면 조건이 효율적으로 적용되고 있음을 의미한다. - 예상 행 수:
rows컬럼을 통해 각 단계에서 처리되는 행 수를 확인하여 병목 지점을 파악한다.
예시 출력:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|----|-------------|-------|------|---------------|------|---------|-----|------|--------------------------|
| 1 | PRIMARY | Trips | ref | driver_id | ... | ... | ... | ... | Using where |
| 2 | SUBQUERY | Users | ref | banned | ... | ... | ... | ... | Using where; Using index |key컬럼: 실제 사용된 인덱스를 나타낸다.Extra컬럼에Using index가 있으면 인덱스만으로 데이터를 조회했음을 의미한다.type이ref또는eq_ref이면 인덱스를 효율적으로 사용한 것이다.
인덱스 확인
Users 테이블의 인덱스 상태는 SHOW INDEX 명령어로 확인할 수 있다.
SHOW INDEX FROM Users;예시 출력:
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Index_type |
|-------|------------|------------|--------------|-------------|------------|
| Users | 0 | PRIMARY | 1 | users_id | BTREE |
| Users | 1 | idx_banned | 1 | banned | BTREE |users_id에 기본 키 인덱스가 있고,banned컬럼에 추가 인덱스가 있다면IN서브쿼리나 조인이 효율적으로 실행된다.- 인덱스가 없으면 전체 테이블 스캔이 발생하므로,
banned컬럼에 인덱스를 추가하는 것을 고려해야 한다.
인덱스 추가 제안
CREATE INDEX idx_banned ON Users(banned);
CREATE INDEX idx_users_id ON Users(users_id);banned컬럼에 인덱스를 추가하면WHERE banned = 'No'조건이 빠르게 처리된다.users_id는 기본 키로 이미 인덱스가 있을 가능성이 높지만, 명시적으로 확인하는 것이 좋다.
최종 개선된 쿼리
WITH filtered AS (
SELECT request_at,
IF(status = 'completed', 0, 1) AS status
FROM Trips t
WHERE t.driver_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.client_id IN (SELECT users_id FROM Users WHERE banned = 'No')
AND t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
)
SELECT request_at AS "Day",
ROUND(SUM(status) / COUNT(*), 2) AS "Cancellation Rate"
FROM filtered
GROUP BY request_at;최종 쿼리의 개선점
- 중간 테이블 최소화:
clean_uidsCTE를 제거하여 중간 결과 저장 비용을 줄였다. - 간결한 조건 처리:
IF함수를 사용하여status를 0 또는 1로 변환하며, MySQL에서 더 빠르게 처리될 가능성이 있다. - 인덱스 활용 최적화:
Users.users_id와Users.banned에 인덱스가 있다면,IN서브쿼리가 효율적으로 실행된다. - 드라이빙 테이블 명확화:
Trips를 드라이빙 테이블로 설정하여 대규모 데이터에 적합하다.
추가 고려사항
- 날짜 인덱스:
Trips.request_at에 인덱스가 있다면WHERE request_at BETWEEN ...조건이 더 효율적이다. 필요 시 아래와 같이 인덱스를 추가한다.CREATE INDEX idx_request_at ON Trips(request_at); - 쿼리 캐싱: 동일한 쿼리가 반복 실행된다면 MySQL의 쿼리 캐시를 활용하거나, 결과를 캐싱하는 애플리케이션 레이어를 고려한다.
- 서브쿼리 vs 조인:
IN서브쿼리는Users테이블이 작을 때 유리하지만, 데이터가 크다면 조인 기반 쿼리가 더 나을 수 있다. 이를 확인하려면EXPLAIN으로 실행 계획을 비교한다.
결론
- 소규모 데이터:
NOT EXISTS를 사용한 쿼리가 조인보다 간결하고 성능이 비슷하거나 더 나을 수 있다. - 대규모 데이터:
IN서브쿼리 또는 조인 기반 쿼리가 인덱스 활용에 따라 더 효율적일 수 있다. 특히Users테이블이 작고 인덱스가 잘 설정되어 있다면IN서브쿼리가 유리하다. - 실행 계획 확인:
EXPLAIN을 사용하여 드라이빙 테이블, 조인 방식, 인덱스 사용 여부를 확인하고, 필요 시 인덱스를 추가한다. - 인덱스 최적화:
Users.banned,Users.users_id,Trips.request_at에 인덱스를 추가하여 필터링과 조인 성능을 개선한다.
이 쿼리들은 데이터 크기와 인덱스 설정에 따라 성능이 달라질 수 있으므로, 실제 환경에서는 EXPLAIN과 SHOW INDEX를 활용하여 최적의 쿼리를 선택해야 한다.
Data Analytics Engineer 가 되