Employees who never changed manager

Medium

Question

The employees table lists the people of a company. The manager_history table records who the manager of an employee was, starting from the day from_date. A new row is added when the manager changes, but also when HR only confirms the same manager again.

Find the employees who always had the same manager: all rows of the employee show the same manager_id. An employee who moved to another manager and then back to the first one has changed manager. Employees with no row in the history are not in the result.

Return employee (the name of the employee), manager (the name of the manager) and since (the first from_date of the employee). Sort by employee.

The order of the rows matters for this question.

group by
having
count distinct
self join
history table
min

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

2 tables

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

employees2 columns · 7 rowsnot run yet

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

Example rows of the employees table
id	name
1	Rohit
2	Sneha
3	Tarun
4	Uma
5	Vikas
6	Wasim
7	Yamini

manager_history4 columns · 10 rowsnot run yet

CREATE TABLE manager_history (id INTEGER PRIMARY KEY, employee_id INTEGER NOT NULL REFERENCES employees(id), manager_id INTEGER NOT NULL REFERENCES employees(id), from_date TEXT NOT NULL);

Example rows of the manager_history table
id	employee_id	manager_id	from_date
1	1	6	2023-01-10
2	2	1	2024-05-01
3	3	1	2025-01-01
4	4	1	2024-06-01
5	4	1	2025-02-01
6	5	2	2023-03-01
7	5	3	2024-03-01
8	5	2	2025-03-01
9	7	3	2024-08-01
10	7	2	2025-08-01

About this SQL

COUNT(DISTINCT x) counts the different non-NULL values. The same table is used twice with two aliases (e for the employee, m for the manager) to get both names. Decide the check first, then join the names: if you add the manager to GROUP BY, every group has exactly one manager and the check is always true. MIN(x) and MAX(x) of a group are equal exactly when the group has one value.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready