Leetcode data query notes (difficult)

Time:2020-1-21

601. The flow of people in the gymnasium

A new gymnasium has been built in city x, and the daily traffic information is recorded in these three columns:Serial number (id)、date (date)、 Visitors flowrate (people)。

Please write a query statement to find the peak period, which requires three consecutive days or more, and the daily traffic is not less than 100.

For example, tablestadium

+------+------------+-----------+
| 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 above example data, 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:
There is only one record per day, and the date increases with the ID.

Ideas / Notes: for the application of self connection, this statement is actually a little tricky, because the dates are all continuous, and the ID belongs to the int type, which is much faster than the operation of to_date (date, ‘yyyymmdd’) / to_char (date, ‘yyyyyymmdd’).

select distinct t1.*
  from stadium t1, stadium t2, stadium t3
 where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
   and ((t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1)
        or (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
        or (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
       )
 order by t1.id

262. Itinerary and users

TripsAll taxi itinerary information is stored in the table. Each trip has a unique key ID. the client ID and driver ID areUsersThe foreign key of users’id in the table. Status is an enumeration type with enumeration members (‘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|
+----+-----------+-----------+---------+--------------------+----------+

UsersTable save for all users. Each user has a unique key users? ID. Banded indicates whether the user is forbidden. Role is an enumeration type indicating (‘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 statement to find outOctober 1, 2013toOctober 3, 2013Cancellation rate of non prohibited users during the period. Based on the above table, your SQL statement should return the following results, and the cancellation rate should be two decimal places.

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

Ideas / Notes: common operation, manual funny

select t.Request_at AS "Day",round(sum(case when t.Status = 'completed' then 0
                                           else 1
                                      end) / count(0),2) AS "Cancellation Rate"
 from trips t
where EXISTS (select u.Users_Id
                from users u
               where u.Users_Id = t.Client_Id
                 and u.Banned = 'No')
  and t.Request_at between '2013-10-01' and '2013-10-03'
group by Day

185. Top three employees in the Department

EmployeeThe table contains all employee information. Each employee has its corresponding ID, salary and department ID.

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

DepartmentThe table contains information about all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find the top three employees in each department. For example, based on the table given above, the query result should return:

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

Ideas / Notes

  1. Scheduling by self connection
  2. Join department table
select d.Name Department, t.Name Employee, Salary
  from (select Name,Salary,DepartmentId,
               (select count(distinct e1.Salary)
                  from Employee e1
                 where e1.DepartmentId = e2.DepartmentId
                   and e1.Salary > e2.Salary) rank
          from Employee e2)t
  left join Department d on d.Id = t.DepartmentId
 where rank < 3
   and d.Id is not null
 order by DepartmentId,Salary desc

summary

The efficiency difference of database operation needs to be at the level of million / ten million / hundred million / one billion, so there will be obvious differences. For example, the data level of bat can only develop its own database. So there are only a lot of basic operations involved here, such as case usage, self connection usage and so on. Specific operations commonly used in projects include designing tables according to normal forms, building indexes according to commonly used fields, partitioning by day / week / month according to data volume, how to transfer data efficiently through stored procedures / functions, how to set undo table space to ensure database performance, how to view commonly used views and statistical tables, how to back up, restore and clean up data, etc., all of which need to be implemented Learning in international projects.