Work Anniversaries Coming Up in October 2025 — SQL Problem
The people team sends a card on every work anniversary. For October 2025, list the current employees (exit_date is NULL) who joined in October of an earlier…
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #230
Problem statement
The people team sends a card on every work anniversary. For October 2025, list the current employees (exit_date is NULL) who joined in October of an earlier year — someone who joined in October 2025 has no anniversary yet.
Return emp_id, full_name, anniversary_date (the date in 2025 that falls on their joining day, as YYYY-MM-DD) and years_completed (2025 minus the joining year). Order the rows by anniversary_date, then emp_id.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| joined_on | date |
| exit_date | date |
Primary key: emp_id.
exit_date is the last working day of someone who has left, or NULL for a current employee.
Examples
Example 1
Employee
| emp_id | full_name | joined_on | exit_date |
|---|---|---|---|
| 501 | Aditi Sharma | 2019-10-14 | NULL |
| 502 | Rahul Nair | 2022-10-03 | NULL |
| 503 | Sneha Patel | 2021-10-14 | 2024-06-30 |
| 504 | Liam Das | 2025-10-06 | NULL |
| 505 | Riya Joshi | 2023-09-30 | NULL |
| 506 | Nikhil Rao | 2024-10-14 | NULL |
Output
| emp_id | full_name | anniversary_date | years_completed |
|---|---|---|---|
| 502 | Rahul Nair | 2025-10-03 | 3 |
| 501 | Aditi Sharma | 2025-10-14 | 6 |
| 506 | Nikhil Rao | 2025-10-14 | 1 |
How to solve Work Anniversaries Coming Up in October 2025
Filter on the parts of the joining date. exit_date IS NULL keeps current employees; MONTH(joined_on) = 10 keeps October joiners; YEAR(joined_on) < 2025 drops this October's new joiners, who have nothing to celebrate yet.
The years completed are 2025 - YEAR(joined_on). The anniversary date can be computed by moving the joining date forward that many years with DATE_ADD(joined_on, INTERVAL n YEAR) — formatted with DATE_FORMAT(…, '%Y-%m-%d') so the column is the plain text date the statement asks for — or assembled directly as CONCAT('2025-', DATE_FORMAT(joined_on, '%m-%d')). October has no 29 February problem, so both agree.
Because every anniversary is in October 2025, ordering by the anniversary date is ordering by the day of the month, which is how the alternative sorts; emp_id breaks ties between people who joined on the same day of different years. A single scan; date-part functions cannot use an index on joined_on, which is fine at HR-table sizes.
Reference solution (MySQL)
SELECT emp_id, full_name,
DATE_FORMAT(DATE_ADD(joined_on, INTERVAL 2025 - YEAR(joined_on) YEAR), '%Y-%m-%d') AS anniversary_date,
2025 - YEAR(joined_on) AS years_completed
FROM Employee
WHERE exit_date IS NULL AND MONTH(joined_on) = 10 AND YEAR(joined_on) < 2025
ORDER BY anniversary_date, emp_idAnother way
SELECT emp_id, full_name, CONCAT('2025-', DATE_FORMAT(joined_on, '%m-%d')) AS anniversary_date,
YEAR('2025-10-01') - YEAR(joined_on) AS years_completed
FROM Employee
WHERE exit_date IS NULL AND DATE_FORMAT(joined_on, '%m') = '10' AND joined_on < '2025-01-01'
ORDER BY DAY(joined_on), emp_id← Referral Bonus Owed to Each Employee · Salary Hike Percentage at the April 2025 Appraisal →