g0201_0300.s0262_trips_and_users.script.sql Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of leetcode-in-java Show documentation

Java-based LeetCode algorithm problem solutions, regularly updated

There is a newer version: 1.37

# Write your MySQL query statement below
# #Hard #Database
SELECT request_at AS "Day",
       ROUND(SUM(CASE
                     WHEN t.status!= 'completed' THEN 1
                     ELSE 0
                 END)/count(*), 2) AS "Cancellation Rate"
FROM Trips t
INNER JOIN Users a ON (a.users_id = t.client_id)
AND (a.banned="No")
INNER JOIN Users b ON (b.users_id = t.driver_id)
AND (b.banned="No")
WHERE request_at BETWEEN "2013-10-01" AND "2013-10-03"
GROUP BY request_at;