Late Punch-Ins per Employee in March 2025 — SQL Problem
The office shift starts at 09:30. The biometric system records one punch per employee per working day, and a punch-in is late when its time of day is after…
- Difficulty: Easy
- Topics: Aggregation, Dates
- Dialect: MySQL
- Problem: #227
Problem statement
The office shift starts at 09:30. The biometric system records one punch per employee per working day, and a punch-in is late when its time of day is after 09:30:00 — a punch at exactly 09:30:00 is on time, one at 09:30:01 is late.
For March 2025 only, return every employee with at least one late punch-in, with the columns emp_id and late_days (the number of late punch-ins that month). Order the rows by late_days descending, then emp_id ascending.
Tables
Table: Punch
| Column | Type |
|---|---|
| punch_id | int |
| emp_id | int |
| punch_in | datetime |
| punch_out | datetime |
Primary key: punch_id.
One row per employee per day they came in. punch_out is NULL when the employee forgot to punch out.
Examples
Example 1
Punch
| punch_id | emp_id | punch_in | punch_out |
|---|---|---|---|
| 1 | 11 | 2025-02-28 09:50:00 | 2025-02-28 18:40:00 |
| 2 | 11 | 2025-03-03 09:42:10 | 2025-03-03 18:30:00 |
| 3 | 11 | 2025-03-04 09:30:00 | 2025-03-04 18:05:00 |
| 4 | 12 | 2025-03-03 09:30:01 | NULL |
| 5 | 12 | 2025-03-04 10:15:00 | 2025-03-04 19:00:00 |
| 6 | 13 | 2025-03-05 09:05:00 | 2025-03-05 17:45:00 |
| 7 | 14 | 2025-03-31 11:02:00 | 2025-03-31 19:30:00 |
| 8 | 14 | 2025-04-01 09:45:00 | 2025-04-01 18:10:00 |
Output
| emp_id | late_days |
|---|---|
| 12 | 2 |
| 11 | 1 |
| 14 | 1 |
How to solve Late Punch-Ins per Employee in March 2025
Filter, then count per employee.
The month filter is a half-open range: punch_in >= '2025-03-01' AND punch_in < '2025-04-01'. It is the safe way to bound a datetime — punch_in <= '2025-03-31' would compare '2025-03-31 11:02:00' with the bare date and drop the whole last day. YEAR(…) = 2025 AND MONTH(…) = 3 is the readable alternative, though it cannot use an index on punch_in.
Lateness is about the time of day only. Turning it into seconds since midnight — HOUR*3600 + MINUTE*60 + SECOND — and comparing with 34,200 (09:30:00) makes the strict boundary obvious: exactly 09:30:00 is not greater, 09:30:01 is. Formatting the time as HH:MM:SS text and comparing with '09:30:00' works too, because fixed-width times sort as text.
Grouping the late rows by emp_id and counting gives late_days; employees with no late day simply form no group (the alternative counts conditionally and removes zero groups with HAVING). The order is fixed by the statement, with emp_id breaking ties. One pass over the month's punches.
Reference solution (MySQL)
SELECT emp_id, COUNT(*) AS late_days
FROM Punch
WHERE punch_in >= '2025-03-01' AND punch_in < '2025-04-01'
AND HOUR(punch_in) * 3600 + MINUTE(punch_in) * 60 + SECOND(punch_in) > 9 * 3600 + 30 * 60
GROUP BY emp_id
ORDER BY late_days DESC, emp_idAnother way
SELECT emp_id, SUM(CASE WHEN DATE_FORMAT(punch_in, '%H:%i:%s') > '09:30:00' THEN 1 ELSE 0 END) AS late_days
FROM Punch
WHERE YEAR(punch_in) = 2025 AND MONTH(punch_in) = 3
GROUP BY emp_id
HAVING late_days > 0
ORDER BY late_days DESC, emp_id← Work E-mail Addresses for New Joiners · Earned Leave Lapsing Above the Carry-Forward Cap →