Employees Overdue for Probation Confirmation — SQL Problem
Every new joiner starts on probation, and HR has to confirm them (or extend the probation) by the probation_end date.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #223
Problem statement
Every new joiner starts on probation, and HR has to confirm them (or extend the probation) by the probation_end date. On the review day, 30 June 2025, HR wants the confirmations that slipped.
Return every employee whose status is still 'probation' and whose probation_end is strictly before 2025-06-30, with the columns emp_id, full_name, probation_end and days_overdue (the number of days from probation_end to 2025-06-30). A probation ending on the review day itself is not overdue. Order the rows by days_overdue descending, then emp_id ascending.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| department | varchar |
| joined_on | date |
| probation_end | date |
| status | enum(probation, confirmed, exited) |
Primary key: emp_id.
One row per employee. probation_end is set when the person joins; status moves to confirmed once HR signs off.
Examples
Example 1
Employee
| emp_id | full_name | department | joined_on | probation_end | status |
|---|---|---|---|---|---|
| 1001 | Aarav Sharma | Engineering | 2024-11-04 | 2025-05-03 | probation |
| 1002 | Diya Iyer | Sales | 2024-12-02 | 2025-06-30 | probation |
| 1003 | Kabir Khan | Finance | 2024-10-01 | 2025-03-30 | confirmed |
| 1004 | Meera Nair | Support | 2024-12-16 | 2025-06-14 | probation |
| 1005 | Rohan Das | Engineering | 2024-11-18 | 2025-05-17 | probation |
| 1006 | Zara Joshi | HR | 2025-01-06 | 2025-07-05 | probation |
| 1007 | Vikram Rao | Design | 2024-09-02 | 2025-03-01 | exited |
Output
| emp_id | full_name | probation_end | days_overdue |
|---|---|---|---|
| 1001 | Aarav Sharma | 2025-05-03 | 58 |
| 1005 | Rohan Das | 2025-05-17 | 44 |
| 1004 | Meera Nair | 2025-06-14 | 16 |
How to solve Employees Overdue for Probation Confirmation
This is a filter with one computed column. Keep the rows where status = 'probation' — confirmed and exited people are no longer anyone's to-do — and where probation_end < '2025-06-30'. Dates stored as YYYY-MM-DD compare correctly as values, and the strict < keeps a probation that ends on the review day itself out of the list, as asked.
DATEDIFF('2025-06-30', probation_end) counts the days between the two dates, later date first, so an overdue confirmation gets a positive number. TIMESTAMPDIFF(DAY, probation_end, '2025-06-30') is the same count with the arguments the other way round; filtering on it being > 0 is equivalent to the date comparison.
The order is the longest overdue first. Because days_overdue falls as probation_end rises, sorting by probation_end ascending gives the same order — the alternative shows that — and emp_id breaks ties when two people share an end date. One scan of the table; an index on (status, probation_end) would turn it into a range read.
Reference solution (MySQL)
SELECT emp_id, full_name, probation_end, DATEDIFF('2025-06-30', probation_end) AS days_overdue
FROM Employee
WHERE status = 'probation' AND probation_end < '2025-06-30'
ORDER BY days_overdue DESC, emp_idAnother way
SELECT emp_id, full_name, probation_end, TIMESTAMPDIFF(DAY, probation_end, '2025-06-30') AS days_overdue
FROM Employee
WHERE status = 'probation' AND TIMESTAMPDIFF(DAY, probation_end, '2025-06-30') > 0
ORDER BY probation_end, emp_id← Daily Admissions With Quiet Days and a Running Total · On-Payroll Headcount by Department and Work Mode →