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.
Your Turn! What are your thoughts on this problem or alternative ways to solve it? Share your own SQL query attempts or insights in the comments below!