Trips and Users

最新推荐文章于 2022-02-17 21:29:06 发布

Goofiness

最新推荐文章于 2022-02-17 21:29:06 发布

阅读量136

点赞数

分类专栏： leetcode mysql 文章标签： leetcode mysql

本文链接：https://blog.csdn.net/byr_wy/article/details/89013742

版权

leetcode 同时被 2 个专栏收录

232 篇文章 0 订阅

订阅专栏

mysql

15 篇文章 0 订阅

订阅专栏

Trips and Users

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 users 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.

Example

±-----------±------------------+
| Day | Cancellation Rate |
±-----------±------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
±-----------±------------------+

Solution

# Write your MySQL query statement below
SELECT T.Request_at AS Day, ROUND(SUM(
  CASE WHEN
       T.Status LIKE "cancelled%" THEN 1
       ELSE 0
  END)/COUNT(*), 2) AS "Cancellation Rate"
  FROM Trips T
  LEFT JOIN Users U ON T.Client_Id = U.Users_Id
 WHERE U.Banned="No" And T.Request_at Between '2013-10-01' AND '2013-10-03'
 GROUP BY T.request_at
 ORDER BY T.request_at