SQLの技術面接対策 - Jitbitのサンプル問題と回答例

🇺🇸セールスエンジニア| Pistachio

2023年3月23日 11:11

最近SQLのスキルアップが必要になったので、勉強用に技術面接対策用の問題などを探していたところ、以下のサイトを見つけました。

Jitbitという会社はヘルプデスクのチケットシステムを販売している会社だそうで、彼らが採用面接の際に候補者に出すSQLのスクリーニング質問をまとめたものになっています。

課題として出されるテーブル構造はとてもシンプルで、かつ問題も割とシンプルです。ですが、実際に解いてみると地味に奥が深かったりして面白かったです！

出題される問題は以下の6問となっています。

上司よりも給料が高い従業員 (名前) を選択する　
部門内で最も給与が高い従業員を選択する 　
3 人未満の部署を選択する
すべての部門と部門の人数を選択する（注意：空の部門を除外して「内部結合」を行うことがよくあります）
同じ部署に上司がいない従業員を選択する
すべての部門と各部門の合計給与を選択する

元記事には正解が無かったので、色々とググりながら試行錯誤してたどり着いた回答をこのnoteではシェアしたいと思います。

また、ER図だけ眺めて答えにたどり着くのには無理があるので、サンプルデータを含むDDLを用意しました。SQLの面接対策で取り組んでみようと思われた方、良ければ使ってくださいね！

以下のSQLはPostgreSQL用に書いたので、他のDBを使われている方は一部表記を変更してください。

テーブルの準備

employees テーブルの作成

CREATE TABLE employees (
employee_id VARCHAR(10) PRIMARY KEY ,
department_id VARCHAR(10),
boss_id VARCHAR(10),
name VARCHAR(255),
salary INTEGER
);

departments テーブルの作成

CREATE TABLE departments (
department_id VARCHAR(10) PRIMARY KEY ,
name VARCHAR(255)
);

employees テーブルにレコードを挿入

INSERT INTO employees (employee_id, department_id, boss_id, name, salary)
VALUES
(1,1,0,'manager',80000),
(2,1,1,'emp1',60000),
(3,1,1,'emp2',50000),
(4,1,1,'emp3',95000),
(5,1,1,'emp4',75000),
(6,2,0,'manager2',75000),
(7,2,6,'emp5',80000),
(8,2,6,'emp6',50000),
(9,3,0,'manager3',70000),
(10,3,6,'emp7',60000);

departments テーブルにレコードを挿入

INSERT INTO departments (department_id, name)
VALUES
(1,'IT'),
(2,'HR'),
(3,'Sales'),
(4,'Marketing');

ここから先は回答例になりますので、まずは自力で解いてみたい方は、上記の内容でチャレンジしてみてください！

様々なアプローチがありますので、私の回答例が100%かというとそうではないと思います。あくまでも、ふ〜ん、そんな考えをして、そう答えにたどり着いたのか～、くらいの参考程度で見ていただければと思います。

回答例

①上司よりも給料が高い従業員 (名前) を選択する

この問題では SELF JOIN (自己結合)が必要になると思います。Employeeテーブル同士を結合させれば、従業員の給料と上司の給料を一つのクエリ結果で比較することができます。

個人的に自己結合は苦手なので、何と何を結合句にすればよいか迷いましたが、最終的にempテーブルからはboss_idを、bossテーブルからはemployee_idを結合句として利用することで、望む結果が得られました。

SELECT emp.name, emp.salary as emp_salary, boss.salary as boss_salary
FROM employees as emp
JOIN employees as boss
ON emp.boss_id = boss.employee_id
WHERE emp.salary > boss.salary;

②部門内で最も給与が高い従業員を選択する

この問題が個人的には一番難しかったです。いきなり2問目でウィンドウ関数を使うことになり焦りました。他の問題ほうが簡単でした。

最初は、GROUP BY と MAX の組み合わせだけでいけるかな～と思っていましたが、それでは「部門内（=部署ごと）」に「最も給料が高い」従業員をうまく出すことができませんでした。

SELECT employee_id,name, salary
FROM
	(SELECT employee_id,
	name,
	salary,
	DENSE_RANK() OVER (PARTITION BY department_id ORDER BY Salary DESC) as ranking
	FROM Employees) as rank_by_dept
WHERE rank_by_dept.ranking = 1

ウィンドウ関数は、以下のQiitaのブログによくまとまっています。

③3 人未満の部署を選択する

「部署」を聞かれているので、部署名が欲しいんでしょうと判断し、こちらの問題でもJOINを使いました。

結果をフィルターするにあたり、WHERE句だとCOUNTのような集計関数と併せて使えないので、HAVINGを使いました。HAVINGだとGROUP BYの後に実行されるので、正しい結果を取得できます。

SELECT d.name
FROM departments as d
JOIN employees as e
ON d.department_id = e.department_id
GROUP BY d.name
HAVING COUNT(e.employee_id) <3

④すべての部門と部門の人数を選択する（注意：空の部門を除外して「内部結合」を行うことがよくあります）

Employeesテーブルと Departmentテーブルを眺めると、実はMarketing部門に所属している従業員がいないということが分かります。

もちろん現実にはそんなことはあり得ないかもしれませんが、問題文にある（注意：空の部門を除外して「内部結合」を行うことがよくあります）の部分を読むにあたり、面接官の意図としては結合の種類をきちんと理解できているか？が知りたいようにも思えます。

以下の私の回答では、存在する全ての部門が表示されるようにEmployees テーブルとDepartmentテーブルを右結合しましたが、従業員が存在している部門のみをリストアップするのであれば、RIGHT JOINの部分をINNER JOINに置き換えればOKです。

SELECT d.name as department_name, COUNT(e.employee_id) as employee_count
FROM employees as e
RIGHT JOIN departments as d
ON e.department_id = d.department_id
GROUP BY d.name

⑤同じ部署に上司がいない従業員を選択

また苦手な自己結合の時間がやってきました。今度は、自己結合の後に、WHERE句で従業員のdepartment_idと、上司のdepartment_idが一致しない人だけを選択しました。

SELECT emp.name
FROM employees as emp
JOIN employees as boss
ON emp.boss_id = boss.employee_id
WHERE emp.department_id != boss.department_id

⑥すべての部門と各部門の合計給与を選択する

この問題が一番簡単でした。部門名と給料が必要なので、例に倣ってEmployeesテーブルとDepartmentsテーブルを結合させます。「すべての」部門を取得したいので、Departmentsテーブルのデータが全て表示されるように結合条件を設定します。

あとは、salaryをSUMで合計し、GROUP BY に部門名を書いて終わりです。

Marketingの合計給与がNULLになるのが気になる方は、関数を使ってNULLを0などに置換しても良いですね。

SELECT d.name, SUM(salary)
FROM employees as e
RIGHT JOIN departments as d
ON e.department_id = d.department_id
GROUP BY d.name;

以上となります！
SQLの実技試験を控えている方や自分の腕試しをしたい方の参考になれば幸いです。

最後までお読みいただきありがとうございました！
スキ・フォローよろしくお願いします☺