[LeetCode] 262. 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 (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.

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

翻譯

• Trips 表格紀錄所有計程車的乘車紀錄，每一筆資料 Id 為唯一值, 其中 Client_Id, Driver_Id 為外部鍵，參照 Users 表格的 Users_Id，Status 為列舉型態，表示三種狀態，分別為”完成乘車”、”司機取消載客”、”乘客取消乘車”。
• Users 表格紀錄所有使用者，每一筆紀錄 Users_Id 為唯一值，其中 Role 欄位為列舉型態，分別為”司機”、”乘客”、”夥伴”。
• 請撰寫一段 SQL 查詢，找出 2013 年 10 月 1 日至 2013 年 10 月 3 日三日的當日乘車取消率，計算公式如下。
• 當日由”司機取消載客”、”乘客取消乘車”乘車紀錄次數 / 當日乘車紀錄總次數，且司機與乘客不得為禁用狀態。

解題思維

TRIPS 以 JOIN 方式關聯 Users 兩次，條件分別為乘客、司機非禁用狀態，時間篩選在 2013 年 10 月 1 日至 2013 年 10 月 3 日三日
再 COUNT, SUM 函數分別對第一步驟的結果計算出乘車紀錄總次數、取消乘車紀錄次數
最後使用 GROUP BY 方式，就可以得出三日當日的乘車取消率

解題報告

Level: Hard
Runtime: 796 ms, faster than 82.29% of Oracle online submissions for Trips and Users.
Memory Usage: 0B, less than 100.00% of Oracle online submissions for Trips and Users.

程式完整解題

/* Write your PL/SQL query statement below */
WITH TAXI AS(SELECT  TRIPS.STATUS       AS STATUS
                    ,TRIPS.REQUEST_AT   AS REQUESTED_DATE
               FROM TRIPS
               JOIN Users CLIENTS
                 ON (CLIENTS.Users_Id = TRIPS.CLIENT_ID)
                AND CLIENTS.BANNED = 'No'
               JOIN Users DRIVERS
                 ON (DRIVERS.Users_Id = TRIPS.CLIENT_ID)
                AND DRIVERS.BANNED = 'No'
              WHERE TO_DATE(TRIPS.REQUEST_AT, 'YYYY-MM-DD') BETWEEN TO_DATE('2013-10-01', 'YYYY-MM-DD') AND TO_DATE('2013-10-03', 'YYYY-MM-DD'))

SELECT TOTAL_REQ.REQ_DATE                                    AS "Day"
      ,ROUND(CANCELLEDL_REQ.TIMES *1.0/ TOTAL_REQ.TIMES, 2)  AS "Cancellation Rate"
  FROM  (SELECT REQUESTED_DATE AS REQ_DATE
               ,COUNT(*)       AS TIMES
          FROM TAXI
          GROUP BY REQUESTED_DATE) TOTAL_REQ
  JOIN(SELECT REQUESTED_DATE  AS REQ_DATE
             ,SUM(CASE WHEN (STATUS = 'cancelled_by_client' OR STATUS = 'cancelled_by_driver') THEN 1
                           ELSE 0 END)      AS TIMES
         FROM TAXI
        GROUP BY REQUESTED_DATE) CANCELLEDL_REQ
    ON TOTAL_REQ.REQ_DATE = CANCELLEDL_REQ.REQ_DATE
 ORDER BY TOTAL_REQ.REQ_DATE

SQL Schema

Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50))
Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner'))
Truncate table Trips
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03')
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03')
Truncate table Users
insert into Users (Users_Id, Banned, Role) values ('1', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('2', 'Yes', 'client')
insert into Users (Users_Id, Banned, Role) values ('3', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('4', 'No', 'client')
insert into Users (Users_Id, Banned, Role) values ('10', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('11', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('12', 'No', 'driver')
insert into Users (Users_Id, Banned, Role) values ('13', 'No', 'driver')