Employee Tenure in Years and Months
Calculate each employee’s completed tenure in years and months as of 2025-01-01.
- Subqueries
- CASE expressions
- Date analysis
- Numeric functions
- Type conversion
Exercise brief
Understand the request
People analytics partner A tenure report needs completed years plus remaining completed months as of a fixed date.
A tenure report needs completed years plus remaining completed months as of a fixed date. Calculate each employee’s completed tenure in years and months as of 2025-01-01.
Return
- Return employee_id, first_name, last_name, hire_date, years, months in this exact left-to-right order.
Constraints
- Adjust total months when the day-of-month anniversary has not occurred.
- Use the fixed reference date.
- Order by years, months, then employee_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameTEXTlast_nameTEXThire_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Compute total months first: (ref_year − hire_year)*12 + (ref_month − hire_month).
Hint 2
years = total_months / 12 (integer division); months = total_months % 12.
Hint 3
Anchor to a fixed reference date so the result never drifts.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH tenure AS (SELECT employee_id, first_name, last_name, hire_date, (CAST(strftime('%Y', '2025-01-01') AS INTEGER) - CAST(strftime('%Y', hire_date) AS INTEGER)) * 12 + (CAST(strftime('%m', '2025-01-01') AS INTEGER) - CAST(strftime('%m', hire_date) AS INTEGER)) - CASE WHEN CAST(strftime('%d', '2025-01-01') AS INTEGER) < CAST(strftime('%d', hire_date) AS INTEGER) THEN 1 ELSE 0 END AS completed_months FROM employees) SELECT employee_id, first_name, last_name, hire_date, completed_months / 12 AS years, completed_months % 12 AS months FROM tenure ORDER BY years DESC, months DESC, employee_id;Why this works
'X years Y months' is the human-friendly tenure format. The trick is to compute a single total-months figure, then split it with integer division (/12) and modulo (%12). MySQL and SQL Server can shortcut total months with TIMESTAMPDIFF(MONTH, …) / DATEDIFF(MONTH, …); Postgres uses AGE.
Success check
The year/month decomposition is anniversary-aware and never drifts with the current date.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | hire_date | years | months |
|---|---|---|---|---|---|
| 1 | Emma | Taylor | 2020-03-15 | 4 | 9 |
| 2 | Michael | Anderson | 2021-06-20 | 3 | 6 |
| 3 | Sarah | Martinez | 2022-01-10 | 2 | 11 |
| 4 | James | Wilson | 2023-09-05 | 1 | 3 |
| 5 | Lisa | Garcia | 2024-02-28 | 0 | 10 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.