Most Recent Hire per Department
MODERATE
Write a PostgreSQL query to identify the most recently hired employee(s) in each department. Display the department name, the employee's name, and their hire date.
Related Concepts
Window Functions (RANK, DENSE_RANK, ROW_NUMBER, MAX OVER) Common Table Expressions (CTEs) JOIN (INNER JOIN) Subqueries (Correlated or in FROM/JOIN) Filtering within Partitions Handling Ties
Hint
This is a classic "greatest-n-per-group" problem. Window functions are very effective here.
- First, you need to determine the latest
hire_datefor each department.- Option A: Use a window function like
MAX(hire_date) OVER (PARTITION BY department_id). - Option B: Use a subquery with
GROUP BY department_idto findMAX(hire_date).
- Option A: Use a window function like
- Then, select employees whose
hire_datematches the latest hire date for their respective department.- If using window functions like
RANK()orDENSE_RANK()partitioned by department and ordered by hire date descending, you can filter where the rank is 1.RANK()orDENSE_RANK()are good for handling ties correctly.
- If using window functions like
- Finally, join with the
Departmentstable to get the department name.
Solution (PostgreSQL)
Imagine you're looking at a list of all employees and their hire dates, grouped by department:
To find the newest person in each department:
- For each department (Engineering, Marketing, Finance), you'd scan through its employees and find the one(s) with the very latest hire date.
- If two people in Engineering were hired on the exact same latest date, you'd list both.
- SQL has special tools called "window functions" that are perfect for this. It's like giving each employee a rank within their department based on how recently they were hired (rank 1 for the newest).
- We then pick all employees who got rank 1 in their department.
PostgreSQL Query (using Window Functions)
-- Find the most recently hired employee(s) in each department
WITH RankedEmployees AS (
SELECT
e.employee_id,
e.name AS employee_name,
e.department_id,
d.department_name,
e.hire_date,
RANK() OVER (
PARTITION BY e.department_id
ORDER BY e.hire_date DESC
) AS rnk
FROM
Employees e
JOIN
Departments d ON e.department_id = d.department_id
)
SELECT
department_name,
employee_name,
hire_date
FROM
RankedEmployees
WHERE
rnk = 1
ORDER BY
department_name,
hire_date DESC,
employee_name;
Explanation of the PostgreSQL Query:
1.
RankedEmployees CTE (Common Table Expression):
- Purpose: To assign a rank to each employee within their department based on their hire date.
SELECT e.employee_id, e.name AS employee_name, ..., d.department_name, e.hire_date: Selects necessary employee and department details.JOIN Departments d ON e.department_id = d.department_id: Joins with theDepartmentstable to get thedepartment_name.RANK() OVER (PARTITION BY e.department_id ORDER BY e.hire_date DESC) AS rnk: This is the window function.PARTITION BY e.department_id: Divides the employees into groups (partitions) based on their department. The ranking is done independently within each department.ORDER BY e.hire_date DESC: Within each department, employees are ordered by theirhire_datein descending order (latest hire date first).RANK(): Assigns a rank based on this order. Employees with the samehire_datewithin the same department will receive the same rank. If there's a tie for the latest date, all tied employees get rank 1. This fulfills the requirement to include all if multiple employees share the same latest hire date. (DENSE_RANK()would also work similarly for this specific "rank = 1" scenario.ROW_NUMBER()would arbitrarily pick one in case of ties.)- The result of this ranking is aliased as
rnk.
2. Final
SELECT Statement:
- Purpose: To select only the most recently hired employees from the ranked list.
SELECT department_name, employee_name, hire_date: Selects the required columns for the final output.FROM RankedEmployees: Queries the results of the CTE.WHERE rnk = 1: Filters the results to include only those employees who received a rank of 1 (i.e., they have the latest hire date within their department).ORDER BY department_name, hire_date DESC, employee_name: Orders the final output for presentation, primarily by department name, then by hire date (though all in the result for a department will have the same latest hire date if usingMAX, or rank 1 implies latest), and finally by employee name for consistency.
Alternative using Subquery with MAX(): Another common way to solve this without an explicit window ranking function is to first find the max hire date per department in a subquery, and then join back to the employees table.
SELECT
d.department_name,
e.name AS employee_name,
e.hire_date
FROM
Employees e
JOIN
Departments d ON e.department_id = d.department_id
JOIN (
SELECT
department_id,
MAX(hire_date) AS max_hire_date
FROM
Employees
GROUP BY
department_id
) max_dates ON e.department_id = max_dates.department_id
AND e.hire_date = max_dates.max_hire_date
ORDER BY
d.department_name,
e.hire_date DESC,
e.name;
Both approaches are valid. Window functions like RANK() are often considered more elegant and can be more efficient for this type of "top-N-per-group" problem, especially when N > 1 or when complex ranking rules are needed.