Intro

Letcodeで無料で解いた3番目の難題を整理しました.

MSSQLで回答しました.

Contents

Human Traffic of Stadium

Input: 
Stadium table:
+------+------------+-----------+
| id   | visit_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-09 | 188       |
+------+------------+-----------+
Output: 
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

は3日連続で100人以上の訪問者のROWに戻ればよい.

WITH ConsecutiveIDs AS (
    SELECT S1.id AS id1, S3.id AS id2
    FROM Stadium AS S1 LEFT JOIN Stadium AS S2
        ON S1.id = S2.id + 1 LEFT JOIN Stadium AS S3
            ON S1.id = S3.id + 2
    WHERE S1.people >= 100 AND
          S2.people >= 100 AND
          S3.people >= 100
)

SELECT DISTINCT S1.*
FROM Stadium as S1, ConsecutiveIDs as CIDs
WHERE id BETWEEN CIDs.id2 AND CIDs.id1

まずWITH問い合わせでセルフサービスを行い,3日間連続で100個を超えるROWのID値のみを取得した.

最大id値と最小idのみをConsectionIDsに入れます.

では、ConsectiveIDs間のID面にIDが表示される.
DISTINTは、重複値

を含むため使用されます.

Department Top Three Salaries

Input: 
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
Output: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

各部門のTOP 3 Salry受信者のみが表示されます.

WITH EMP_DE AS (
    SELECT E.name as EName, E.salary, D.name as DName
    FROM Employee AS E LEFT JOIN 
        Department AS D
        ON E.departmentId = D.id
)


SELECT Department, Employee, Salary
FROM (
    SELECT DName as Department,
       EName as Employee, 
       salary as Salary,
       DENSE_RANK() OVER(
           PARTITION BY DName ORDER BY salary DESC
       ) as rnk
    FROM EMP_DE    
) as list
WHERE rnk <= 3

EMP DEを使用して、2つのテーブルJOINのWITH文を作成します.

FROMサブqueryでrankを求める.

DENSE RANK:重複ランキング&スキップランキングXが存在します.

PARTIONBY:rankは部門別に購入する必要があります.

WHEREセクションでは、3つ以下のrankのみが表示されます.

Trips and Users

Input: 
Trips table:
+----+-----------+-----------+---------+---------------------+------------+
| 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 |
+----+-----------+-----------+---------+---------------------+------------+
Users table:
+----------+--------+--------+
| 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 |
+----------+--------+--------+
Output: 
+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33              |
| 2013-10-02 | 0.00              |
| 2013-10-03 | 0.50              |
+------------+-------------------+

「2013-10-01」~「2013-10-03」期間中はキャンセル率が要求される.

banのユーザはカウントしない.

小数点

の2番目のビットに表示されます.

WITH UnbannedClient AS (
    SELECT *
    FROM Users
    WHERE banned != 'Yes'
        AND role = 'client'
),
UnbannedDriver AS (
    SELECT *
    FROM Users
    WHERE banned != 'Yes'
        AND role = 'driver'
)



SELECT
        T.request_at AS Day,
        CONVERT(
            NUMERIC(3,2),
            AVG(CASE WHEN T.status LIKE 'cancelled%' THEN 1.0 ELSE 0 END), 2
        ) as "Cancellation Rate"
FROM Trips as T LEFT JOIN UnbannedClient AS UC
        ON T.client_id = UC.users_id
        LEFT JOIN UnbannedDriver AS UD
            ON T.driver_id = UD.users_id 
WHERE UC.users_id is not null
        AND UD.users_id is not null
        AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

クライアントとドライバは禁止されていません.WITH文を使用してください.

FROM&WHERE節はJOINが未封のみを残す記録+持続時間である.

GROUP BYに日付別にグループ化します.

CONVERT+NUMERICの組み合わせで、小数点の2番目のビットまで表示できます.

率を求めるのはAVGを利用して平均値を求めるのです.

キャンセルが含まれている場合は、0(非1)を平均とします.

Outro

私の答えは最高の答えではないかもしれません.

アルゴリズムとは異なり、sqlは無料で練習するのは難しい.
例えば

Hackerrankでは、条件が明確でないために難しい問題があるのを覚えています.