Item 10: Travel and Users (Difficulty: Difficulty)

The Trips table contains travel information for all taxis. Each trip has a unique key Id, Client_Id and Driver_Id are the foreign keys of Users_Id in the Users table. Status is an enumeration type with enumeration members ('completed','cancelled_by_driver','cancelled_by_client').

The Users table stores all users. Each user has a unique key Users_Id. Banned indicates whether the user is banned, while Role is an enumeration type for ('client','driver','partner').

Write an SQL statement to find out the cancellation rate of non-prohibited users between October 1, 2013 and October 3, 2013. Based on the table above, your SQL statement should return the following results, with Cancellation Rate retaining two decimal places.

-- Create table Trips And insert data
Create table If Not Exists Trips 
(
    Id INT(5),
    Client_Id INT(5),
    Driver_Id INT(5), 
    City_Id INT(5), 
    Status ENUM('completed','cancelled_by_driver', 'cancelled_by_client'),
    Request_at varchar(50)
);
INSERT INTO Trips(Id, Client_Id, Driver_Id,City_Id, Status, Request_at)  
           VALUES
                 ('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');
-- Create table Users And insert data
Create table If Not Exists Users 
(
    Users_Id INT(5),
    Banned VARCHAR(50), 
    Role ENUM('client', 'driver', 'partner')
);
INSERT INTO Users (Users_Id, Banned, Role)
            VALUES 
                  ('1', 'No', 'client'),
                  ('2', 'Yes', 'client'),
                  ('3', 'No', 'client'),
                  ('4', 'No', 'client'),
                  ('10', 'No', 'driver'),
                  ('11', 'No', 'driver'),
                  ('12', 'No', 'driver'),
                  ('13', 'No', 'driver');
-- Query the Cancellation Rate of Non-Prohibited Users from October 1, 2013 to October 3, 2013
SELECT request_at,
       ROUND(SUM(CASE WHEN Status="completed" THEN 0 ELSE 1 END)/COUNT(*),2) 
       AS Cancellation_Rate
    FROM 
    (
      SELECT * FROM Trips 
          WHERE client_id NOT IN
          (
            SELECT users_id FROM Users 
            WHERE banned = "yes" and role = "client"
           ) 
          AND request_at >= "2013-10-01" 
          AND request_at <= "2013-10-03"
    ) AS t
    GROUP BY request_at 
    ORDER BY request_at ASC;

Item 11: Top 3 high-paid employees in each department (Difficulty: Medium)

Empty the employee table in Item 7 and insert the following data again (actually insert 5,6 rows more):

Write an SQL query to find the top three employees in each department. For example, based on the given table above, the query results should be returned:

In addition, consider realizing the functions of employees with high salaries in the former N departments.

-- Delete previously created Employee Table data, re-insert data
TRUNCATE TABLE Employee;
INSERT INTO Employee(Id, Name, Salary, DepartmentId)
              VALUES
                    (1, "Joe", 70000, 1),
                    (2, "Henry", 80000, 2),
                    (3, "Sam", 60000, 2),
                    (4, "Max", 90000, 1),
                    (5, "Janet", 69000, 1),
                    (6, "Randy", 85000, 1);
-- Save the two tables as new tables by inlinking them with department numbers h ，Find out the top three salaries by divisions
CREATE TABLE h AS
    SELECT d.Name Department, e.Name Employee, e.Salary 
        FROM Employee e INNER JOIN Department d
            ON e.DepartmentId = d.Id;
SELECT Department, h1.Employee, h1.Salary
    FROM h h1
    WHERE 3 >   
           (
            SELECT COUNT(DISTINCT h2.Salary) 
            FROM h h2
            WHERE h2.Salary > h1.Salary AND h1.Department = h2.Department
            )
    ORDER BY Department, h1.Salary DESC;

Item 12 Score Ranking - (Difficulty: Medium)

It is still the scoring table of Item 9 to achieve the ranking function, but the ranking is not continuous, as follows:

-- Establish score Table and insert data
CREATE TABLE score
(
	Id INT(10) PRIMARY KEY,
	Score FLOAT(4)
);
INSERT INTO score(Id, Score)
           VALUES(1, 3.50),
                 (2, 3.65),
                 (3, 4.00),
                 (4, 3.85),
                 (5, 4.00),
                 (6, 3.65);
```sql
-- Establish score Table and insert data
CREATE TABLE score
(
	Id INT(10) PRIMARY KEY,
	Score FLOAT(4)
);
INSERT INTO score(Id, Score)
           VALUES(1, 3.50),
                 (2, 3.65),
                 (3, 4.00),
                 (4, 3.85),
                 (5, 4.00),
                 (6, 3.65);
-- If the score of the left table is less than that of the right table, the left table is joined by the left table. id After grouping, the number of scores in the right table is counted.+1
SELECT s1.Score, COUNT(s2.Score) + 1 Rank
    FROM Score s1 LEFT JOIN Score s2
        ON s1.Score < s2.Score
    GROUP BY s1.Id
    ORDER BY s1.Score DESC;