[LeetCode] 262. Trips and Users

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH Clients AS (
        SELECT *
        FROM Users
        WHERE role = 'client')
    , Drivers AS (
        SELECT *
        FROM Users
        WHERE role = 'driver')

SELECT t.request_at AS Day
     , ROUND(COUNT(DISTINCT CASE WHEN t.status != 'completed' THEN t.id END) / COUNT(DISTINCT t.id), 2) AS 'Cancellation Rate'
FROM Trips AS t
    INNER JOIN Clients AS c ON t.client_id = c.users_id
    INNER JOIN Drivers AS d ON t.driver_id = d.users_id
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
  AND c.banned = 'No'
  AND d.banned = 'No'
GROUP BY t.request_at