LeetCode 数据库262,601整理_letcode262-CSDN博客

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        |
+------------+-------------------+

SQL脚本

select DISTINCT T.Request_at as Day,
round(count(case when T.status<>'completed' then T.status else null end)/count(1),2) as "Cancellation Rate"      //别名中含有空格，用双引号表示，否则会syntax error
from ( 
select Request_at 
,Driver_Id 
,Client_Id 
,Status 
from Trips where Request_at between '2013-10-01' and '2013-10-03') T 
inner join (select Users_Id,Banned from Users) U 
on (U.Banned = 'NO' and T.Client_Id = U.Users_Id) 
group by 1;

601. Human Traffic of Stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

Note:Each day only have one row record, and the dates are increasing with id increasing.

SQL脚本

这题的难度属于hard级别，但我觉得最多也就是一般难度。初看起来这题好像有点不知所措，但仔细分析却能发现其实条件很简单,只要满足以下任意三个条件即可：

1.id in (x,x+1,x+2) 的记录的people >= 100;

2.1.id in (x,x+1,x-1) 的记录的people >= 100;

3.1.id in (x,x-1,x-2) 的记录的people >= 100;

SELECT *
FROM   stadium a
WHERE  a.people >= 100
       AND ( ( a.id + 1 IN (SELECT id
                            FROM   stadium
                            WHERE  people >= 100)
               AND a.id + 2 IN (SELECT id
                                FROM   stadium
                                WHERE  people >= 100) )
              OR ( a.id - 1 IN (SELECT id
                                FROM   stadium
                                WHERE  people >= 100)
                   AND a.id + 1 IN (SELECT id
                                    FROM   stadium
                                    WHERE  people >= 100) )
              OR ( a.id - 1 IN (SELECT id
                                FROM   stadium
                                WHERE  people >= 100)
                   AND a.id - 2 IN (SELECT id
                                    FROM   stadium
                                    WHERE  people >= 100) ) );