[LeetCode] 262. Trips and Users

Trips and Users - LeetCode

 

취소율은 금지되지 않은 사용자가 있는 취소된(클라이언트별 또는 드라이버별) 요청의 수를 해당일의 금지되지 않은 사용자가 있는 요청의 총 수로 나누어 계산됩니다.
"2013-10-01"에서 "2013-10-03" 사이의 금지되지 않은 사용자(클라이언트와 드라이버 모두 금지되지 않아야 함)가 있는 요청의 취소율을 찾기 위한 솔루션을 매일 작성하십시오. (소수점 두 개까지)
순서에 상관없이 결과 테이블을 반환합니다.

 

SELECT request_at Day
      , ROUND(COUNT(CASE WHEN status <> 'completed' THEN id END) / COUNT(id), 2) 'Cancellation Rate'
FROM trips
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND client_id NOT IN (SELECT users_id
                      FROM users
                      WHERE banned = 'Yes')
AND driver_id NOT IN (SELECT users_id
                      FROM users
                      WHERE banned = 'Yes')
GROUP BY request_at

 

2023/10/13 다시 풀어봄

WITH cte_all AS(
      SELECT request_at as Day
            , COUNT(*) as request
            , COUNT(CASE WHEN status <> "completed" THEN id END) as cancel
      FROM trips
      WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
          AND client_id IN (SELECT users_id
                            FROM users
                            where banned = "No")
          AND driver_id IN (SELECT users_id
                            FROM users
                            where banned = "No")
      GROUP BY Day
)
SELECT Day
      , ROUND(cancel/request, 2) as "Cancellation Rate"
FROM cte_all

WITH cte_all AS(
      SELECT request_at
            , SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) as cancel
            , COUNT(*) as total 
      FROM trips as t
      INNER JOIN users uc ON t.client_id = uc.users_id
      INNER JOIN users ud ON t.driver_id = ud.users_id
      WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
            AND uc.banned = 'No'
            AND ud.banned = 'No'
      GROUP BY request_at
)
SELECT request_at as Day
      , ROUND(cancel/total, 2) as "Cancellation Rate"
FROM cte_all

 

💡 개념정리

 

1. 원하는 기간의 결과를 구하기 위해 BETWEEN A AND B 사용

 

2. banned된 사용자는 집계에서 제외하기 위해 다중행 서브쿼리를 사용

➡️ users 테이블에서 banned가 Yes인 사용자의 users_id를 구하고, 해당 id가 trips 테이블의 client_id, driver_id에 있으면 제외한다. (NOT IN)

 

 

3. 취소율을 구하기 위해 (status가 completed가 아닌 id의 수 / 전체 id의 수)를 해준 후 소수점 자리를 맞추어주기 위해 ROUND 사용

 

적재적소에 서브쿼리를 사용하는 것이 아직 어렵다😅

 

---

➕ 2023/10/13

SELECT request_at as Day
      , COUNT(*) as request
      , COUNT(CASE WHEN status <> "completed" THEN id END) as cancel
FROM trips
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'
AND client_id IN (SELECT users_id
                  FROM users
                  where banned = "No")
AND driver_id IN (SELECT users_id
                  FROM users
                  where banned = "No")
GROUP BY Day

좀 더 직관적으로 문제를 해결하기 위해 request와 cancel의 수를 구해준 후, 임시테이블을 사용하여 취소율을 구해주었다. 오랜만에 cte구문을 사용하니 재미있었다😊

 

SELECT request_at
        , SUM(CASE WHEN status <> 'completed' THEN 1 ELSE 0 END) as cancel
        , COUNT(*) as total 
FROM trips as t
INNER JOIN users uc ON t.client_id = uc.users_id
INNER JOIN users ud ON t.driver_id = ud.users_id
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
    AND uc.banned = 'No'
    AND ud.banned = 'No'
GROUP BY request_at

➡️ 이 코드는 서브쿼리가 없어서 비교적 식을 알아보기 편하고 CASE WHEN 구문에서 원하는 조건에는 1, 그렇지 않은 조건에는 0을 주고 SUM을 함으로써 수식을 알아보기 편하다. 대신 조인과정이 조금 복잡하다는 점!

---

📍본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.