Leetcode - 262. Trips and Users

1. 문제

 

 

 

Table: Trips

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| client_id   | int      |
| driver_id   | int      |
| city_id     | int      |
| status      | enum     |
| request_at  | date     |     
+-------------+----------+
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". Round Cancellation Rate to two decimal points.

Return the result table in any order.

 

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

 

 

2. 문제 조건

 

1. cancellation rate : the number of canceled (by client or driver) requests with unbanned users / the total number of requests with unbanned users

 

-> unbanned user만을 이용하여야 하는데, ban여부를 판단하는 테이블에 client와 driver가 섞여있음.

-> 따라서 WITH 문을 통해서 unbanned user를 필터링 한 임시 테이블 생성 후 WHERE 조건으로 걸러내기

 

2. each day between "2013-10-01" and "2013-10-03" -> WHERE

 

3. Round Cancellation Rate to two decimal points.

 

3. 코드 작성

 

-- unbanned user 테이블
WITH unbanned AS (
    SELECT users_id
    FROM users
    WHERE banned = 'No'
)

SELECT request_at AS Day
     , ROUND(COUNT(DISTINCT CASE WHEN status LIKE 'cancelled%' THEN id ELSE NULL END) / COUNT(DISTINCT id), 2) AS 'Cancellation Rate'
FROM trips
WHERE client_id IN (SELECT users_id FROM unbanned)
  AND driver_id IN (SELECT users_id FROM unbanned)
  AND request_at BETWEEN "2013-10-01" AND "2013-10-03"
GROUP BY Day

 

 

 

4. 회고

 

• WITH문을 통해서 unbanned user를 필터링하는 아이디어까지는 좋았으나, WITH문이 임시 테이블이라는 걸 깜빡했다. subquery로 users_id를 뽑아내지 않고, 그대로 python의 리스트처럼 사용하는 바람에 잠깐 막혔었다. 다음에 WITH문을 쓸 때에는 같은 실수를 반복하지 말아야겠다.

실수한 코드

-- unbanned user 테이블
WITH unbanned AS (
    SELECT users_id
    FROM users
    WHERE banned = 'No'
)

SELECT request_at AS Day
     , ROUND(COUNT(DISTINCT CASE WHEN status LIKE 'cancelled%' THEN id ELSE NULL END) / COUNT(DISTINCT id), 2) AS 'Cancellation Rate'
FROM trips
WHERE client_id IN unbanned -- subquery로 users_id를 뽑아줘야됨
  AND driver_id IN unbanned -- subquery로 users_id를 뽑아줘야됨
  AND request_at BETWEEN "2013-10-01" AND "2013-10-03"
GROUP BY Day