SQL基本→中級の練習に最適！ Hackerrank SQL 全問解答集【難易度Medium】

Hackerrankとは

Hackerrankは、世界中のエンジニアが共通のプログラミング問題に挑戦できるサービスで、問題を解いたスコアを競ったり、コードテストを受験して証明書を発行したりもできます。

What is HackerRank?
HackerRank is a place where programmers from all over the world come together to solve problems in a wide range of ? Computer Science domains such as algorithms, machine learning, or artificial intelligence, as well as to >practice different programming paradigms like functional programming.
FAQ | Hackerrank

コードテスト準備用として用意されているPrepareと呼ばれる問題集は、ブラウザからコードを提出して結果を確認できるため学習用として便利です。

Hackerrank SQL Prepareを問題集として利用する

中でも、SQLの難易度Medium, Hardは学習用にちょうど良い難易度・問題数と感じましたので、答え合わせ用に全問解答集を用意しました。

Hackerrankの問題文は全て英語です。「英語を読むのがしんどいよ」という方は、問題内容や題意を全て日本語で解説している私のブログ記事をご覧ください。
Qiitaユーザーのリテラシーの高さを考慮すると、ブログ記事の説明はくどいかと思いますので、本記事では問題に一言コメントをつけるに留めています。

難易度 Medium 全16問

難易度Mediumは、完全初心者向けのSQL問題から一歩踏み出して、複数のテーブルからデータを集めたり、典型的なテクニックの利用方法を確認できたりする問題が揃っています。

難易度表（独断）

問題	難易度	備考	解答例
Binary Tree Node	★★	シンプルに考えると平易	解答
THE PADS	★★	基本の集計	解答
Occupations	★★★	行→列の展開	解答
Weather Observation Station 18	★	Basicレベル	解答
Weather Observation Station 19	★	Basicレベル	解答
Weather Observation Station 20	★	Basicレベル	解答
The Report	★★	基本の集計	解答
Top Competitors	★★★★★	情報量の多さが難	解答
Contest Leaderboard	★★★	間違いポイントあり	解答
Challenges	★★★★	出力に対する要求がやや難	解答
Olivander's Inventory	★★★★	題意の読み取りがやや難	解答
Placement	★★★	サブクエリの使い方	解答
New Companies	★★	見た目によらず平易	解答
Symmetric Pairs	★★★	サブクエリの使い方	解答
SQL Project Planning	★★★★	時系列・レコード間比較	解答
Print Prime Numbers	★★★	知っているかどうかの類	解答

本記事のSQLクエリは、oracleでの提出を確認しています。
Hackerrank上では、その他Mycrosoft SQL, MySql, DB2が利用できるようです。

Binary Tree Node

二分探索木の話が出てきますが、特にアルゴリズム的知識は必要ありません。

をテーブルで何とか表そうとすると、

Node	Parent
1	Null
2	1
3	1
4	2
5	2
6	3
7	3

になるという理解があれば十分なようです。

解答例

oracle

SELECT
  N,
  CASE
    WHEN P is NULL THEN 'Root'
    WHEN EXISTS(
      SELECT
        *
      FROM
        BST BST2
      WHERE
        BST2.P = BST1.N
    ) THEN 'Inner'
    ELSE 'Leaf'
  END
FROM
  BST BST1
ORDER BY
  BST1.N;

The PADS

Professor(教授)、Actor(俳優)、Doctor（医師）、Signer（歌手）の4種類の職業の人が、それぞれ何人いるか集計する問題です。

解答例

oracle

SELECT
  Name || '(' || CASE
    WHEN Occupation = 'Professor' THEN 'P'
    WHEN Occupation = 'Actor' THEN 'A'
    WHEN Occupation = 'Doctor' THEN 'D'
    WHEN Occupation = 'Singer' THEN 'S'
    ELSE NULL
  END || ')'
FROM
  OCCUPATIONS
ORDER BY
  Name;
SELECT
  'There are a total of ' || COUNT() || CASE
    WHEN Occupation = 'Professor' THEN ' professors.'
    WHEN Occupation = 'Actor' THEN ' actors.'
    WHEN Occupation = 'Doctor' THEN ' doctors.'
    WHEN Occupation = 'Singer' THEN ' singers.'
    ELSE NULL
  END
FROM
  OCCUPATIONS
GROUP BY
  Occupation
ORDER BY
  COUNT(),
  Occupation;

Occupations

表を集計し、行方向から列方向に展開し直して出力する問題です。

解答例

oracle

SELECT
  MAX(
    CASE
      WHEN Occupation = 'Doctor' THEN Name
      ELSE NULL
    END
  ),
  MAX(
    CASE
      WHEN Occupation = 'Professor' THEN Name
      ELSE NULL
    END
  ),
  MAX(
    CASE
      WHEN Occupation = 'Singer' THEN Name
      ELSE NULL
    END
  ),
  MAX(
    CASE
      WHEN Occupation = 'Actor' THEN Name
      ELSE NULL
    END
  )
FROM(
    SELECT
      ROW_NUMBER() OVER(
        PARTITION BY Occupation
        ORDER BY
          Name
      ) RN,
      Name,
      Occupation
    FROM
      OCCUPATIONS
  )
GROUP BY
  RN
ORDER BY
  RN;

Weather Observation Station 18

SQLの算術演算を使用する問題です。

解答例

oracle

SELECT
  ROUND(
    ABS(MAX(LAT_N) - MIN(LAT_N)) + ABS(MAX(LONG_W) - MIN(LONG_W)),
    4
  )
FROM
  STATION;

Weather Observation Station 19

SQLの算術演算を使用する問題です。

解答例

oracle

SELECT
  ROUND(
    SQRT(
      POWER(MAX(LAT_N) - MIN(LAT_N), 2) + POWER(MAX(LONG_W) - MIN(LONG_W), 2)
    ),
    4
  )
FROM
  STATION;

Weather Observation Station 20

SQLの算術演算を使用する問題です。

解答例

oracle

SELECT
  ROUND(MEDIAN(LAT_N), 5)
FROM
  STATION;

The Report

２つのテーブルを使用した、試験成績の集計を行う問題です。

解答例

oracle

SELECT
  CASE
    WHEN Marks >= 70 THEN Name ELSE NULL
  END,
  (
    SELECT
      Grade
    FROM
      Grades
    WHERE
      Marks >= Grades.Min_Mark
      AND Marks <= Grades.Max_Mark
  ) AS G,
    Marks
  FROM
    Students
  ORDER BY
    G DESC,
    Name,
    Marks;

Top Competitors

プログラミングコンテストを題材にした問題です。

ヒント

問題に完全に正答していない場合でも、部分点が存在するため、得点が0になるとは限らない。

解答例

oracle

CREATE VIEW PerfectScorer(hacker_id, name) AS
SELECT
  CASE
    WHEN SUB1.score = (
      SELECT
        score
      FROM
        Challenges
        INNER JOIN Difficulty ON Challenges.difficulty_level = Difficulty.difficulty_level
      WHERE
        SUB1.challenge_id = challenge_id
    ) THEN SUB1.hacker_id
    ELSE NULL
  END AS full_score_hacker_id,
  (
    SELECT
      name
    FROM
      Hackers
    WHERE
      Hackers.hacker_id = SUB1.hacker_id
  )
FROM
  Submissions SUB1;
SELECT
  hacker_id,
  MAX(name)
FROM
  PerfectScorer
WHERE
  (hacker_id IS NOT NULL)
GROUP BY
  hacker_id
HAVING
  COUNT(hacker_id) > 1
ORDER BY
  COUNT(hacker_id) DESC,
  hacker_id;

Contest Leaderboard

指定されている出力の仕方に注意しましょう。

解答例

oracle

CREATE VIEW Max_Scores(hacker_id, name, max_score) AS
SELECT
  MAX(hacker_id),
  MAX(
    (
      SELECT
        name
      FROM
        Hackers
      WHERE
        Hackers.hacker_id = Submissions.hacker_id
    )
  ),
  MAX(score)
FROM
  Submissions
WHERE
  score > 0
GROUP BY
  hacker_id,
  challenge_id;
SELECT
  hacker_id,
  MAX(name),
  SUM(max_score)
FROM
  Max_Scores
GROUP BY
  hacker_id
ORDER BY
  SUM(max_score) DESC,
  hacker_id;

Challenges

出力形式の指定がトリッキーなので注意しましょう。

解答例

oracle

CREATE VIEW Count_Each_Student_Posts(hacker_id, name, Cnt) AS
SELECT
  Hackers.hacker_id,
  MAX(name),
  COUNT(challenge_id)
FROM
  Hackers
  INNER JOIN Challenges ON Hackers.hacker_id = Challenges.hacker_id
GROUP BY
  Hackers.hacker_id
ORDER BY
  COUNT(challenge_id) DESC,
  Hackers.hacker_id;
SELECT
  *
FROM
  (
    SELECT
      CASE
        WHEN (
          SELECT
            COUNT(*)
          FROM
            Count_Each_Student_Posts C2
          WHERE
            C1.Cnt = C2.Cnt
          GROUP BY
            Cnt
        ) = 1 THEN hacker_id
        ELSE CASE
          WHEN Cnt = (
            SELECT
              MAX(Cnt)
            FROM
              Count_Each_Student_Posts
          ) THEN hacker_id
          ELSE NULL
        END
      END hacker_id_2,
      name,
      Cnt
    FROM
      Count_Each_Student_Posts C1
  )
WHERE
  hacker_id_2 IS NOT NULL
ORDER BY
  Cnt DESC,
  hacker_id_2;

Olivander's Inventory

題意が問題文から読み取りづらいことがこの問題を難しくしています。

ヒント

出力例を先に確認して、杖のpower, age, coins_neededのうちどれが優先されているか確かめてみましょう。

解答例

oracle

CREATE VIEW W(id, age, coins_needed, power, is_evil) AS
SELECT
  id,
  age,
  coins_needed,
  power,
  is_evil
FROM
  Wands INNER JOIN Wands_property
  ON Wands.code = Wands_property.code;

SELECT
  id,
  age,
  coins_needed,
  power
FROM
  W
WHERE is_evil = 0
  AND coins_needed =
  (
    SELECT
      MIN(coins_needed)
    FROM W W2
    WHERE W.age = W2.age
      AND W.power = W2.power
  )
ORDER BY
  power DESC,
  age DESC;

Placement

サブクエリをWHERE句で使用します。

解答例

oracle

SELECT
  Name
FROM
  Students Std1
WHERE
  (
    SELECT
      Salary
    FROM
      Packages P1
    WHERE
      P1.ID = Std1.ID
  ) < (
    SELECT
      Salary
    FROM
      Packages P2
    WHERE
      P2.ID = (
        SELECT
          Friend_ID
        FROM
          Friends
        WHERE
          Std1.ID = Friends.ID
      )
  )
ORDER BY
  (
    SELECT
      Salary
    FROM
      Packages P2
    WHERE
      P2.ID = (
        SELECT
          Friend_ID
        FROM
          Friends
        WHERE
          Std1.ID = Friends.ID
      )
  );

New Companies

組織図は以下のようになっており、会社にかかわらず共通です。

ヒント

会社が合併するという設定がありますが、出力は会社IDごとに行うので、全く気にする必要がありません。

解答例

oracle

SELECT
  company_code,
  founder,
  (
    SELECT
      COUNT(DISTINCT lead_manager_code)
    FROM
      Lead_Manager
    WHERE
      Lead_Manager.company_code = Company.company_code
    GROUP BY
      company_code
  ),
  (
    SELECT
      COUNT(DISTINCT senior_manager_code)
    FROM
      Senior_Manager
    WHERE
      Senior_Manager.company_code = Company.company_code
    GROUP BY
      company_code
  ),
  (
    SELECT
      COUNT(DISTINCT manager_code)
    FROM
      Manager
    WHERE
      Manager.company_code = Company.company_code
    GROUP BY
      company_code
  ),
  (
    SELECT
      COUNT(DISTINCT employee_code)
    FROM
      Employee
    WHERE
      Employee.company_code = Company.company_code
    GROUP BY
      company_code
  )
FROM
  Company
ORDER BY
  company_code;

Symmetric Pairs

単一のテーブルのレコード同士を比較する問題です。

ヒント

EXISTS句の基本的な使い方を適用できる問題です。

解答例

oracle

CREATE VIEW Functions_with_Rownumber(R, X, Y) AS
SELECT
  ROW_NUMBER() OVER(
    ORDER BY
      X
  ),
  X,
  Y
FROM
  Functions;
SELECT
  X,
  Y
FROM
  Functions_with_Rownumber F1
WHERE
  EXISTS(
    SELECT
      *
    FROM
      Functions_with_Rownumber F2
    WHERE
      F1.X = F2.Y
      AND F1.Y = F2.X
      AND F1.R < F2.R
  )
ORDER BY
  R;

SQL Project Planning

タスクの時系列データを時間軸で集計する問題です。

ヒント

WINDOW関数の使用法を思い出しましょう。

解答例

oracle

CREATE VIEW V(Start_Date, End_Date, period) AS
SELECT
  Start_Date,
  End_Date,
  SUM(not_consecutive_flg) OVER(
    ORDER BY
      Start_Date RANGE UNBOUNDED PRECEDING
  )
FROM
  (
    SELECT
      Start_Date,
      End_Date,
      CASE
        WHEN MIN(End_Date) OVER(
          ORDER BY
            Start_Date ROWS BETWEEN 1 PRECEDING
            AND 1 PRECEDING
        ) IS NULL THEN 1
        ELSE CASE
          WHEN Start_Date - MIN(End_Date) OVER(
            ORDER BY
              Start_Date ROWS BETWEEN 1 PRECEDING
              AND 1 PRECEDING
          ) > 0 THEN 1
          ELSE 0
        END
      END not_consecutive_flg
    FROM
      Projects
  );
SELECT
  MIN(Start_Date),
  MAX(End_Date)
FROM
  V
GROUP BY
  period
ORDER BY
  COUNT(*),
  MIN(Start_Date);

Print Prime Numbers

入力テーブルがない問題です。1000以下の素数を列挙します。

ヒント

素数判定をする前に、判定する対象の数字をデータとして保持するテーブルが必要です。

解答例

oracle

CREATE TABLE Digits(digit INTEGER NOT NULL);
INSERT INTO
  Digits
VALUES
  (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE VIEW Sequence(seq) AS
SELECT
  D1.digit + 10(D2.digit) + 100(D3.digit)
FROM
  Digits D1
  CROSS JOIN Digits D2
  CROSS JOIN Digits D3;CREATE VIEW PrimeNums(R, p) AS
SELECT
  ROW_NUMBER() OVER(
    ORDER BY
      seq
  ),
  seq
FROM
  Sequence S1
WHERE
  (
    NOT EXISTS(
      SELECT
        *
      FROM
        Sequence S2
      WHERE
        S1.seq > S2.seq
        AND S1.seq % S2.seq = 0
        AND S2.seq > 1
    )
    AND S1.seq BETWEEN 2
    AND 1000
  )
  OR S1.seq = 2;
SELECT
  GROUP_CONCAT(p separator '&')
FROM
  PrimeNums;