CodeKata

SQL

LeetCode: Trips and Users

• 작성한 쿼리

with unbanned_user as (
    select
      id
      , request_at
      , status
    from
      Trips t
      join Users u1
      on t.client_id = u1.users_id
      join Users u2
      on t.driver_id = u2.users_id
    where
      u1.banned = 'No'
      and u2.banned = 'No'
      and request_at between '2013-10-01' and '2013-10-03'
)
, count_request as(
    select
      request_at
      , count(request_at) as total
      , sum(if(status='completed', 1, 0)) as completed
    from
      unbanned_user
    group by
      request_at
)
select
  request_at as "Day"
  , round(1-(completed/total), 2) as "Cancellation Rate"
from
  count_request
;

참고할 만한 다른 풀이

SELECT request_at as Day, ROUND(SUM(CASE WHEN status IN ('cancelled_by_driver', 'cancelled_by_client') 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'
GROUP BY request_at
HAVING request_at BETWEEN '2013-10-01' AND '2013-10-03'

SELECT request_at AS Day,
    ROUND(SUM(IF(status != 'completed', 1, 0)) / COUNT(*) ,2) AS "Cancellation Rate"
FROM Trips
WHERE client_id IN (SELECT users_id FROM Users WHERE banned='No')
AND driver_id IN (SELECT users_id FROM Users WHERE banned='No')
AND request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at

SELECT
  Trips.request_at AS Day,
  ROUND(SUM(Trips.status != 'completed') / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
INNER JOIN Users AS Clients
  ON (Trips.client_id = Clients.users_id)
INNER JOIN Users AS Drivers
  ON (Trips.driver_id = Drivers.users_id)
WHERE Clients.banned = 'No'
  AND Drivers.banned = 'No'
  AND Trips.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 1;

최종 프로젝트

• EDA 그래프 수정
• 발표 대본 작성
• 발표 영상 찍기
• 내일 발표 준비

회고

• SQL 문제가 너무 어려워서 Python 알고리즘 문제를 못 풀었다… 아쉽다
• 뭔가 정신없는 하루였음