# Time: O((t * u) + tlogt)
# Space: O(t)
#
# The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id
# are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of
# (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).
#
# +----+-----------+-----------+---------+--------------------+----------+
# | Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
# +----+-----------+-----------+---------+--------------------+----------+
# | 1 | 1 | 10 | 1 | completed |2013-10-01|
# | 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
# | 3 | 3 | 12 | 6 | completed |2013-10-01|
# | 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
# | 5 | 1 | 10 | 1 | completed |2013-10-02|
# | 6 | 2 | 11 | 6 | completed |2013-10-02|
# | 7 | 3 | 12 | 6 | completed |2013-10-02|
# | 8 | 2 | 12 | 12 | completed |2013-10-03|
# | 9 | 3 | 10 | 12 | completed |2013-10-03|
# | 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
# +----+-----------+-----------+---------+--------------------+----------+
# The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of
# (‘client’, ‘driver’, ‘partner’).
#
# +----------+--------+--------+
# | Users_Id | Banned | Role |
# +----------+--------+--------+
# | 1 | No | client |
# | 2 | Yes | client |
# | 3 | No | client |
# | 4 | No | client |
# | 10 | No | driver |
# | 11 | No | driver |
# | 12 | No | driver |
# | 13 | No | driver |
# +----------+--------+--------+
# Write a SQL query to find the cancellation rate of requests made by unbanned clients between
# Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following
# rows with the cancellation rate being rounded to two decimal places.
#
# +------------+-------------------+
# | Day | Cancellation Rate |
# +------------+-------------------+
# | 2013-10-01 | 0.33 |
# | 2013-10-02 | 0.00 |
# | 2013-10-03 | 0.50 |
# +------------+-------------------+
#
select
t.Request_at Day,
round(sum(case when t.Status = 'completed' then 0 else 1 end) / count(*), 2) Rate
from Trips t
inner join Users u
on t.Client_Id = u.Users_Id and u.Banned = 'No'
where t.Request_at between '2013-10-01' and '2013-10-03'
group by t.Request_at
MySQL.zip （42个子文件） 
MySQL 
average-salary-departments-vs-company.sql 683B find-cumulative-salary-of-an-employee.sql 609B nth-highest-salary.sql 822B rank-scores.sql 1KB not-boring-movies.sql 1KB customer-placing-the-largest-number-of-orders.sql 320B department-highest-salary.sql 2KB department-top-three-salaries.sql 2KB consecutive-available-seats.sql 222B combine-two-tables.sql 988B get-highest-answer-rate-question.sql 189B exchange-seats.sql 1KB managers-with-at-least-5-direct-reports.sql 247B tree-node.sql 209B find-median-given-frequency-of-numbers.sql 488B students-report-by-geography.sql 623B delete-duplicate-emails.sql 806B human-traffic-of-stadium.sql 453B sales-person.sql 295B rising-temperature.sql 844B employees-earning-more-than-their-managers.sql 1KB triangle-judgement.sql 116B second-degree-follower.sql 168B customers-who-never-order.sql 1KB winning-candidate.sql 264B trips-and-users.sql 3KB friend-requests-i-overall-acceptance-rate.sql 296B shortest-distance-in-a-line.sql 644B median-employee-salary.sql 511B employee-bonus.sql 315B shortest-distance-in-a-plane.sql 222B big-countries.sql 1KB friend-requests-ii-who-has-the-most-friends.sql 258B swap-salary.sql 769B biggest-single-number.sql 253B find-customer-referee.sql 101B classes-more-than-5-students.sql 795B investments-in-2016.sql 450B count-student-number-in-departments.sql 269B duplicate-emails.sql 529B consecutive-numbers.sql 889B second-highest-salary.sql 698B资源评论
