Employees Averaging Less Than Eight Hours a Day

Medium
232.

Employees Averaging Less Than Eight Hours a Day

MediumDatesAggregation

MySQL

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

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 on the same date, or NULL when they forgot to punch out.

Example

The rows must come back in the order shown.

Punch

punch_idemp_idpunch_inpunch_out
1212025-05-05 09:30:002025-05-05 17:00:00
2212025-05-06 09:45:002025-05-06 17:30:00
3212025-05-07 10:00:00NULL
4222025-05-05 09:00:002025-05-05 18:00:00
5222025-05-06 09:15:002025-05-06 16:15:00
6232025-05-05 09:20:002025-05-05 17:20:00
7242025-04-30 09:00:002025-04-30 13:00:00
8242025-05-02 08:50:002025-05-02 16:10:00
9252025-05-09 11:00:00NULL

Output

emp_iddays_countedavg_minutes
241440
212458
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 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

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 on the same date, or NULL when they forgot to punch out.

Examples

Example 1

Punch

punch_idemp_idpunch_inpunch_out
1212025-05-05 09:30:002025-05-05 17:00:00
2212025-05-06 09:45:002025-05-06 17:30:00
3212025-05-07 10:00:00NULL
4222025-05-05 09:00:002025-05-05 18:00:00
5222025-05-06 09:15:002025-05-06 16:15:00
6232025-05-05 09:20:002025-05-05 17:20:00
7242025-04-30 09:00:002025-04-30 13:00:00
8242025-05-02 08:50:002025-05-02 16:10:00
9252025-05-09 11:00:00NULL

Output

emp_iddays_countedavg_minutes
241440
212458

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_id

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