Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit615ff78

Browse files
add 262
1 parent7a518c9 commit615ff78

File tree

2 files changed

+52
-0
lines changed

2 files changed

+52
-0
lines changed

‎README.md

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -617,6 +617,7 @@ Your ideas/fixes/algorithms are more than welcome!
617617
|577|[Employee Bonus](https://leetcode.com/problems/employee-bonus/)|[Solution](../master/database/_577.sql)||| Easy|
618618
|574|[Winning Candidate](https://leetcode.com/problems/winning-candidate/)|[Solution](../master/database/_574.sql)||| Medium|
619619
|570|[Managers with at Least 5 Direct Reports](https://leetcode.com/problems/managers-with-at-least-5-direct-reports/)|[Solution](../master/database/_570.sql)||| Medium|
620+
|262|[Trips and Users](https://leetcode.com/problems/trips-and-users/)|[Solution](../master/database/_262.sql)||| Hard| Inner Join
620621
|197|[Rising Temperature](https://leetcode.com/problems/rising-temperature/)|[Solution](../master/database/_197.sql)| O(n^2)|O(n)| Easy|
621622
|196|[Delete Duplicate Emails](https://leetcode.com/problems/delete-duplicate-emails/)|[Solution](../master/database/_196.sql)| O(n^2)|O(n)| Easy|
622623
|184|[Department Highest Salary](https://leetcode.com/problems/department-highest-salary)|[Solution](../master/database/_184.sql)| O(n^2)|O(n)| Medium|

‎database/_262.sql

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,51 @@
1+
--262. Trips and Users
2+
--The Trips table holds all taxi trips.
3+
--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’).
4+
--
5+
--+----+-----------+-----------+---------+--------------------+----------+
6+
--| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
7+
--+----+-----------+-----------+---------+--------------------+----------+
8+
--| 1 | 1 | 10 | 1 | completed |2013-10-01|
9+
--| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
10+
--| 3 | 3 | 12 | 6 | completed |2013-10-01|
11+
--| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
12+
--| 5 | 1 | 10 | 1 | completed |2013-10-02|
13+
--| 6 | 2 | 11 | 6 | completed |2013-10-02|
14+
--| 7 | 3 | 12 | 6 | completed |2013-10-02|
15+
--| 8 | 2 | 12 | 12 | completed |2013-10-03|
16+
--| 9 | 3 | 10 | 12 | completed |2013-10-03|
17+
--| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
18+
--+----+-----------+-----------+---------+--------------------+----------+
19+
--The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).
20+
--
21+
--+----------+--------+--------+
22+
--| Users_Id | Banned | Role |
23+
--+----------+--------+--------+
24+
--| 1 | No | client |
25+
--| 2 | Yes | client |
26+
--| 3 | No | client |
27+
--| 4 | No | client |
28+
--| 10 | No | driver |
29+
--| 11 | No | driver |
30+
--| 12 | No | driver |
31+
--| 13 | No | driver |
32+
--+----------+--------+--------+
33+
--Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013.
34+
--For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
35+
--
36+
--+------------+-------------------+
37+
--| Day | Cancellation Rate |
38+
--+------------+-------------------+
39+
--| 2013-10-01 | 0.33 |
40+
--| 2013-10-02 | 0.00 |
41+
--| 2013-10-03 | 0.50 |
42+
--+------------+-------------------+
43+
--Credits:
44+
--Special thanks to @cak1erlizhou for contributing this question, writing the problem description and adding part of the test cases.
45+
46+
selectt.Request_atas Day,
47+
round(sum(case whent.Statuslike'cancelled_%' then1 else0 end)/count(*),2)as'Cancellation Rate'
48+
from Trips t
49+
inner join Users u
50+
ont.Client_Id=u.Users_Idandu.Banned='No'
51+
wheret.Request_at between'2013-10-01'and'2013-10-03'group byt.Request_at

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp