[LeetCode-HARD] Trips and Users 미확인 사용자의 일별 cancellation rate 구하기

LeetCode - 262. Trips and Users

Difficulty 분류 : Hard

Lessons Learned

MySQL 서브쿼리 alias 지정 필수

Every derived table must have its own alias
서브쿼리에 alias(이름)이 지정되지않았을 때 생기는 에러 (MySQL)
⇒ 서브쿼리에 alias(이름)을 지정해 해결한다.

조건에 맞는 경우 세기 CASE WHEN - THEN - ELSE - END

ROUND((SUM(CASE 
           WHEN status != 'completed' 
           THEN 1 
           ELSE 0 
           END) / COUNT(*)), 2)  AS `Cancellation Rate`

• ELSE 0 작성하지 않으면 조건에 맞지 않는 경우는 디폴트로 NULL이 리턴된다.
• Output 컬럼 Alias 에 공백이 존재할 때 ` 로 묶어준다.

WHERE 절 Semi-Join

SubQuery 대신 WHERE절 조건에 SELECT문을 사용해서 조건에 맞는 데이터만 불러온다.

My Solution

/*
- cancellation rate 
  - the number of canceled (by client or driver) requests withunbanned users 
    divided by
  - the total number of requests with unbanned users on that day.

- cancellation rate of requests with unbanned users 
- (both client and driver must not be banned) : banned = "No"
- each day between "2013-10-01" and "2013-10-03". 
- Round Cancellation Rate to two decimal points.
- Return the result table in any order.

*/


SELECT request_at AS `Day`,

    /* WITHOUT ELSE 0 STATEMENT -> NULL */
    /* ALIAS WITH SPACE : `` */
    ROUND((SUM(CASE 
               WHEN status != 'completed' 
               THEN 1 
               ELSE 0 
               END) / COUNT(*)), 2)  AS `Cancellation Rate`

FROM Trips
WHERE 

     /* Unbanned Users */
     (client_id IN ( SELECT users_id FROM Users WHERE banned = "No" ))
    AND
     (driver_id IN ( SELECT users_id FROM Users WHERE banned = "No" ))
    AND

    /* Date */
     (request_at BETWEEN "2013-10-01" AND "2013-10-03" )

GROUP BY 1 /* Daily Cancellation Rate */