Calculate Completed Employee Tenure Years
Calculate each employee’s completed years of service as of 2025-01-01.
- CASE expressions
- Date analysis
- Type conversion
- Sorting
Exercise brief
Understand the request
People analytics partner A service-award review needs completed tenure years as of a fixed reporting date.
A service-award review needs completed tenure years as of a fixed reporting date. Calculate each employee’s completed years of service as of 2025-01-01.
Return
- Return employee_id, first_name, last_name, hire_date, years_employed in this exact left-to-right order.
Constraints
- Use the fixed date 2025-01-01 so results do not drift.
- Subtract one year when the 2025 anniversary has not occurred.
- Order longest tenure first, 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
Start with the difference between the reference year and hire year.
Hint 2
Compare month-day values to determine whether the anniversary has happened.
Hint 3
Subtract one when the reference month-day is earlier than the hire month-day.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT employee_id, first_name, last_name, hire_date, CAST(strftime('%Y', '2025-01-01') AS INTEGER) - CAST(strftime('%Y', hire_date) AS INTEGER) - CASE WHEN strftime('%m-%d', '2025-01-01') < strftime('%m-%d', hire_date) THEN 1 ELSE 0 END AS years_employed FROM employees ORDER BY years_employed DESC, employee_id;Why this works
Completed-year tenure is anniversary based. Dividing elapsed days by 365 or 365.25 fails around leap years and dates just before an anniversary.
Success check
Tenure reflects completed anniversaries rather than an approximation based on 365.25 days.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | hire_date | years_employed |
|---|---|---|---|---|
| 1 | Emma | Taylor | 2020-03-15 | 4 |
| 2 | Michael | Anderson | 2021-06-20 | 3 |
| 3 | Sarah | Martinez | 2022-01-10 | 2 |
| 4 | James | Wilson | 2023-09-05 | 1 |
| 5 | Lisa | Garcia | 2024-02-28 | 0 |
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.