SQL Quest › SQL Interview Questions › Joins

Same-Year Hires in Same Department (Self Join)

MediumFreeQuerying BasicsJoinsDate Functions

An onboarding analyst wants to identify employees who joined the same department in the same year — natural peer cohorts for ramp comparisons. Use a self join.

Show emp_a, emp_b, department, hire_year. Each pair should appear once (not mirrored) — use e1.emp_id < e2.emp_id to enforce one-direction pairing. Order by hire_year, department, emp_a.

The < trick is the standard way to deduplicate pairs in a self join. Without it you'd get both (Alice, Bob) and (Bob, Alice) for every pair, which is twice the noise.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

employees

emp_id	name	department	position	salary	hire_date	manager_id	performance_rating
1	Alice Johnson	Engineering	Senior Developer	95000	2019-03-15	5	4.5
2	Bob Smith	Engineering	Developer	75000	2020-06-01	1	3.8
3	Carol Williams	Marketing	Marketing Manager	85000	2018-09-20	NULL	4.2

Expected output: Alice | Bob | Engineering | 2020

Hint

FROM employees e1 JOIN employees e2 ON e1.department = e2.department AND strftime('%Y', e1.hire_date) = strftime('%Y', e2.hire_date) WHERE e1.emp_id < e2.emp_id. The id-inequality kills mirror pairs.

Concepts

SELECT JOIN Self Join Date Functions

Practise the topic: SQL practice questions · JOIN practice · Date function practice

In these company practice sets

Plaid · Revolut

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Same-Year Hires in Same Department (Self Join)

Schema

Hint

Concepts

In these company practice sets

Related questions