Late Punch-Ins per Employee in March 2025

Easy
227.

Late Punch-Ins per Employee in March 2025

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

ColumnType
punch_idint
emp_idint
punch_indatetime
punch_outdatetime

Primary key: punch_id.

One row per employee per day they came in. punch_out is NULL when the employee forgot to punch out.

Example

The rows must come back in the order shown.

Punch

punch_idemp_idpunch_inpunch_out
1112025-02-28 09:50:002025-02-28 18:40:00
2112025-03-03 09:42:102025-03-03 18:30:00
3112025-03-04 09:30:002025-03-04 18:05:00
4122025-03-03 09:30:01NULL
5122025-03-04 10:15:002025-03-04 19:00:00
6132025-03-05 09:05:002025-03-05 17:45:00
7142025-03-31 11:02:002025-03-31 19:30:00
8142025-04-01 09:45:002025-04-01 18:10:00

Output

emp_idlate_days
122
111
141
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.

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

ColumnType
punch_idint
emp_idint
punch_indatetime
punch_outdatetime

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_idemp_idpunch_inpunch_out
1112025-02-28 09:50:002025-02-28 18:40:00
2112025-03-03 09:42:102025-03-03 18:30:00
3112025-03-04 09:30:002025-03-04 18:05:00
4122025-03-03 09:30:01NULL
5122025-03-04 10:15:002025-03-04 19:00:00
6132025-03-05 09:05:002025-03-05 17:45:00
7142025-03-31 11:02:002025-03-31 19:30:00
8142025-04-01 09:45:002025-04-01 18:10:00

Output

emp_idlate_days
122
111
141

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_id

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