男女成人秒免费试看久久_无码毛片免费自慰09_黄品汇mba旧版本_亚洲少妇与大狗性交对白_人妻无卡中文乱码在线_无码a级毛片免费视频内谢_午夜亚洲亚洲精品不卡_欧美成人在线观看_欧美日本韩国乱伦_十八禁观看污污污网站

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.

+------------+-------------------+

|     Day    | Cancellation Rate |

+------------+-------------------+

| 2013-10-01 |       0.33        |

| 2013-10-02 |       0.00        |

| 2013-10-03 |       0.50        |

+------------+-------------------+

SELECT Request_at as Day, ROUND(SUM(t.Status != "completed") / COUNT(*), 2) as "Cancellation Rate"

    FROM Trips t 

    JOIN Users c ON t.Client_ID = c.Users_ID AND c.Banned = "No"

    JOIN Users d ON t.Driver_ID = d.Users_ID AND d.Banned = "No"

    WHERE Request_at BETWEEN "2013-10-01" AND "2013-10-03"

    GROUP BY Request_at;

【 Median Employee Salary】

The Employee table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.

+-----+------------+--------+

|Id   | Company    | Salary |

+-----+------------+--------+

|1    | A          | 2341   |

|2    | A          | 341    |

|3    | A          | 15     |

|4    | A          | 15314  |

|5    | A          | 451    |

|6    | A          | 513    |

|7    | B          | 15     |

|8    | B          | 13     |

|9    | B          | 1154   |

|10   | B          | 1345   |

|11   | B          | 1221   |

|12   | B          | 234    |

|13   | C          | 2345   |

|14   | C          | 2645   |

|15   | C          | 2645   |

|16   | C          | 2652   |

|17   | C          | 65     |

+-----+------------+--------+

Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions.

SELECT

    Employee.Id, Employee.Company, Employee.Salary

FROM

    Employee,

    Employee alias

WHERE

    Employee.Company = alias.Company

GROUP BY Employee.Company , Employee.Salary

HAVING SUM(CASE

    WHEN Employee.Salary = alias.Salary THEN 1

    ELSE 0

END) >= ABS(SUM(SIGN(Employee.Salary - alias.Salary)))

ORDER BY Employee.Id

;

【Managers with at Least 5 Direct Reports】

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+------+----------+-----------+----------+

|Id    |Name   |Department |ManagerId |

+------+----------+-----------+----------+

|101   |John   |A       |null      |

|102   |Dan   |A       |101       |

|103   |James   |A       |101       |

|104   |Amy   |A       |101       |

|105   |Anne   |A       |101       |

|106   |Ron   |B       |101       |

+------+----------+-----------+----------+

SELECT

    Name

FROM

    Employee AS t1 JOIN

    (SELECT                 //沒(méi)想到

        ManagerId

    FROM

        Employee

    GROUP BY ManagerId

    HAVING COUNT(ManagerId) >= 5) AS t2

    ON t1.Id = t2.ManagerId

;

---------------------