Employees Averaging Less Than Eight Hours a Day
Each punch row is one working day: in and out on the same date.
- Difficulty: Medium
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #232
Problem statement
Each punch row is one working day: in and out on the same date. HR's attendance review looks at May 2025 and flags people whose average day was shorter than 8 hours (480 minutes). A day without a punch_out cannot be measured and is ignored entirely.
Return emp_id, days_counted (measured days in May) and avg_minutes (the average minutes between punch-in and punch-out, rounded to a whole number) for every employee whose unrounded average is below 480. Order the rows by avg_minutes ascending, 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 on the same date, or NULL when they forgot to punch out.
Examples
Example 1
Punch
| punch_id | emp_id | punch_in | punch_out |
|---|---|---|---|
| 1 | 21 | 2025-05-05 09:30:00 | 2025-05-05 17:00:00 |
| 2 | 21 | 2025-05-06 09:45:00 | 2025-05-06 17:30:00 |
| 3 | 21 | 2025-05-07 10:00:00 | NULL |
| 4 | 22 | 2025-05-05 09:00:00 | 2025-05-05 18:00:00 |
| 5 | 22 | 2025-05-06 09:15:00 | 2025-05-06 16:15:00 |
| 6 | 23 | 2025-05-05 09:20:00 | 2025-05-05 17:20:00 |
| 7 | 24 | 2025-04-30 09:00:00 | 2025-04-30 13:00:00 |
| 8 | 24 | 2025-05-02 08:50:00 | 2025-05-02 16:10:00 |
| 9 | 25 | 2025-05-09 11:00:00 | NULL |
Output
| emp_id | days_counted | avg_minutes |
|---|---|---|
| 24 | 1 | 440 |
| 21 | 2 | 458 |
How to solve Employees Averaging Less Than Eight Hours a Day
Measure each day, then average per employee.
A day with no punch_out has no length, and the statement says to ignore it — not to count it as zero. Filtering punch_out IS NOT NULL in WHERE removes it from both the count and the average. (AVG would skip a NULL difference anyway, but COUNT(*) would not, so the filter keeps both honest.) The month is bounded with a half-open range on punch_in.
TIMESTAMPDIFF(MINUTE, punch_in, punch_out) gives each day's minutes; AVG of it per emp_id is the average day. The threshold is a condition on a group, so it goes in HAVING, and it uses the unrounded average — an average of 479.6 minutes is short even though it rounds to 480. The displayed avg_minutes is rounded to a whole number, which keeps every engine's decimals out of the answer.
The alternative adds the minutes up from the hour and minute parts (punch in and out are on the same date) and compares total < 480 × days, which avoids division in the test altogether. One pass over the month's punches.
Reference solution (MySQL)
SELECT emp_id, COUNT(*) AS days_counted, ROUND(AVG(TIMESTAMPDIFF(MINUTE, punch_in, punch_out))) AS avg_minutes
FROM Punch
WHERE punch_out IS NOT NULL AND punch_in >= '2025-05-01' AND punch_in < '2025-06-01'
GROUP BY emp_id
HAVING AVG(TIMESTAMPDIFF(MINUTE, punch_in, punch_out)) < 480
ORDER BY avg_minutes, emp_idAnother way
SELECT emp_id, days_counted, ROUND(total_minutes / days_counted) AS avg_minutes
FROM (
SELECT emp_id, COUNT(punch_out) AS days_counted,
SUM((HOUR(punch_out) - HOUR(punch_in)) * 60 + MINUTE(punch_out) - MINUTE(punch_in)) AS total_minutes
FROM Punch
WHERE YEAR(punch_in) = 2025 AND MONTH(punch_in) = 5
GROUP BY emp_id
) t
WHERE days_counted > 0 AND total_minutes < 480 * days_counted
ORDER BY avg_minutes, emp_id← Salary Hike Percentage at the April 2025 Appraisal · Appraisal Rating Mix by Department →