Helix Systems' HR team tracks employee seniority within each department based on hire date — the earliest hire in a department holds rank 1.
Write a query to return the ID, name, department ID, and hire date of every employee, plus the employee's seniority rank within their department.
Assumptions:
- The
employeestable has one row per employee with anid, aname, adepartment_id, and ahire_date. - Within each department, the earliest
hire_datereceives seniority rank1, the next-earliest receives2, and so on. Rank values restart for each department. - Every employee in a department receives a different seniority rank; if two employees share the same
hire_date, they receive consecutive numbers in some order.
Output:
- One row per employee, with columns
id,name,department_id,hire_date, andseniority_rank.
Schema · hr 4 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
name,
department_id,
hire_date,
ROW_NUMBER() OVER (
PARTITION BY
department_id
ORDER BY
hire_date
) AS seniority_rank
FROM
employeesThe shape
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) numbers each department's employees from earliest hire to latest, starting at 1 per department. The longest-tenured employee in any department gets 1, the next-hired gets 2, and the sequence runs independently inside every department.
Clause by clause
SELECT id, name, department_id, hire_date, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY hire_date) AS seniority_rankreturns each employee's identifying columns and their seniority position.PARTITION BY department_idcreates one window per department;ORDER BY hire_date(ascending, the default) sorts within each window from earliest to latest hire date;ROW_NUMBERassigns the positions1, 2, 3, ...per department.FROM employeesreads every employee. No filter; every employee gets a seniority rank inside their own department.
Why ascending ORDER BY hire_date and not descending
The earliest hire date is the smallest value in the column, so ascending order puts the most-tenured person first. Descending order would put the newest hire at rank 1, which inverts the seniority semantics. Whenever the rank is meant to read as "rank 1 = most senior," the sort direction has to match how the underlying column orders the population. For hire dates, earliest is most senior, so ascending is the right direction.
The trap
If two employees in the same department share an exact hire_date, ROW_NUMBER will give them different positions arbitrarily. Whoever the database engine returns first gets 1 and the other gets 2, with no semantic reason for the choice. If the seniority report needs a deterministic tie-break, add a secondary sort like ORDER BY hire_date, id. If the report needs tied employees to share a rank, switch to RANK or DENSE_RANK. ROW_NUMBER is the right tool when the consumer needs exactly one employee per position, and the tie-break can be arbitrary.
You practiced ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... ASC) — the earliest-or-latest-per-group shape, partitioned by an entity column and ordered by a timestamp.
