trips-and-users

java

python

python3

mysql

mssql

oraclesql

csharp

javascript

typescript

bash

swift

kotlin

dart

golang

ruby

scala

rust

racket

erlang

elixir

pythondata

postgresql

cangjie

Database

Table: <code>Trips</code>

<pre>
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | varchar | 
+-------------+----------+
id is the primary key (column with unique values) for this table.
The table holds all taxi trips. Each trip has a unique id, while client_id and driver_id are foreign keys to the users_id at the Users table.
Status is an ENUM (category) type of (&#39;completed&#39;, &#39;cancelled_by_driver&#39;, &#39;cancelled_by_client&#39;).
</pre>

&nbsp;

Table: <code>Users</code>

<pre>
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id is the primary key (column with unique values) for this table.
The table holds all users. Each user has a unique users_id, and role is an ENUM type of (&#39;client&#39;, &#39;driver&#39;, &#39;partner&#39;).
banned is an ENUM (category) type of (&#39;Yes&#39;, &#39;No&#39;).
</pre>

&nbsp;

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Write a solution to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between <code>&quot;2013-10-01&quot;</code> and <code>&quot;2013-10-03&quot;</code> with at least one trip. Round <code>Cancellation Rate</code> to two decimal points.

Return the result table in any order.

The&nbsp;result format is in the following example.

&nbsp;
Example 1:

<pre>
Input: 
Trips table:
+----+-----------+-----------+---------+---------------------+------------+
| 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 |
+----+-----------+-----------+---------+---------------------+------------+
Users table:
+----------+--------+--------+
| 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 |
+----------+--------+--------+
Output: 
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
Explanation: 
On 2013-10-01:
 - There were 4 requests in total, 2 of which were canceled.
 - However, the request with Id=2 was made by a banned client (User_Id=2), so it is ignored in the calculation.
 - Hence there are 3 unbanned requests in total, 1 of which was canceled.
 - The Cancellation Rate is (1 / 3) = 0.33
On 2013-10-02:
 - There were 3 requests in total, 0 of which were canceled.
 - The request with Id=6 was made by a banned client, so it is ignored.
 - Hence there are 2 unbanned requests in total, 0 of which were canceled.
 - The Cancellation Rate is (0 / 2) = 0.00
On 2013-10-03:
 - There were 3 requests in total, 1 of which was canceled.
 - The request with Id=8 was made by a banned client, so it is ignored.
 - Hence there are 2 unbanned request in total, 1 of which were canceled.
 - The Cancellation Rate is (1 / 2) = 0.50
</pre>

trips_and_users

Trips and Users

Hopper Company Queries I

hopper-company-queries-i

Hopper 公司查询 I

Hopper Company Queries II

hopper-company-queries-ii

Hopper 公司查询 II

Hopper Company Queries III

hopper-company-queries-iii

Hopper 公司查询 III

表：<code>Trips</code>
<div class="original__bRMd">
<div>
<pre>
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| id | int |
| client_id | int |
| driver_id | int |
| city_id | int |
| status | enum |
| request_at | varchar | 
+-------------+----------+
id 是这张表的主键（具有唯一值的列）。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
</pre>

&nbsp;

<div class="original__bRMd">
<div>
表：<code>Users</code>
</div>
</div>

<pre>
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| users_id | int |
| banned | enum |
| role | enum |
+-------------+----------+
users_id 是这张表的主键（具有唯一值的列）。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。
</pre>

&nbsp;

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

编写解决方案找出&nbsp;<code>"2013-10-01"</code>&nbsp;至&nbsp;<code>"2013-10-03"</code>&nbsp;期间有&nbsp;至少&nbsp;一次行程的非禁止用户（乘客和司机都必须未被禁止）的 取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 <code>Cancellation Rate</code> 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

结果格式如下例所示。

&nbsp;

示例 1：

<pre>
输入： 
Trips 表：
+----+-----------+-----------+---------+---------------------+------------+
| 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 |
+----+-----------+-----------+---------+---------------------+------------+
Users 表：
+----------+--------+--------+
| 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 |
+----------+--------+--------+
输出：
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
解释：
2013-10-01：
 - 共有 4 条请求，其中 2 条取消。
 - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。
 - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。
 - 取消率为 (1 / 3) = 0.33
2013-10-02：
 - 共有 3 条请求，其中 0 条取消。
 - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。
 - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。
 - 取消率为 (0 / 2) = 0.00
2013-10-03：
 - 共有 3 条请求，其中 1 条取消。
 - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。
 - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。
 - 取消率为 (1 / 2) = 0.50
</pre>
</div>
</div>