Employees Overdue for Probation Confirmation

Easy
223.

Employees Overdue for Probation Confirmation

EasyBasicsDates

MySQL

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

ColumnTypeValues
emp_idint
full_namevarchar
departmentvarchar
joined_ondate
probation_enddate
statusenumprobation, 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.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namedepartmentjoined_onprobation_endstatus
1001Aarav SharmaEngineering2024-11-042025-05-03probation
1002Diya IyerSales2024-12-022025-06-30probation
1003Kabir KhanFinance2024-10-012025-03-30confirmed
1004Meera NairSupport2024-12-162025-06-14probation
1005Rohan DasEngineering2024-11-182025-05-17probation
1006Zara JoshiHR2025-01-062025-07-05probation
1007Vikram RaoDesign2024-09-022025-03-01exited

Output

emp_idfull_nameprobation_enddays_overdue
1001Aarav Sharma2025-05-0358
1005Rohan Das2025-05-1744
1004Meera Nair2025-06-1416
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
full_namevarchar
departmentvarchar
joined_ondate
probation_enddate
statusenum(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_idfull_namedepartmentjoined_onprobation_endstatus
1001Aarav SharmaEngineering2024-11-042025-05-03probation
1002Diya IyerSales2024-12-022025-06-30probation
1003Kabir KhanFinance2024-10-012025-03-30confirmed
1004Meera NairSupport2024-12-162025-06-14probation
1005Rohan DasEngineering2024-11-182025-05-17probation
1006Zara JoshiHR2025-01-062025-07-05probation
1007Vikram RaoDesign2024-09-022025-03-01exited

Output

emp_idfull_nameprobation_enddays_overdue
1001Aarav Sharma2025-05-0358
1005Rohan Das2025-05-1744
1004Meera Nair2025-06-1416

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_id

Another 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 →