Lab Tests Slower Than Their Usual Turnaround

Medium
211.

Lab Tests Slower Than Their Usual Turnaround

MediumSubqueriesDates

MySQL

Different tests take different times — a CBC is quick, a blood culture is not — so the lab compares each report with the average turnaround of the same test. Turnaround is the whole minutes from collected_at to reported_at; reports still pending (reported_at NULL) are ignored everywhere, including in the averages.

Return result_id, test_name and turnaround_minutes of every report whose turnaround is strictly greater than its test's average turnaround. Order by test_name, then result_id.

Tables

Table: LabResult

ColumnType
result_idint
patient_idint
test_namevarchar
collected_atdatetime
reported_atdatetime

Primary key: result_id.

One row per test run. Times are recorded to the minute; reported_at is NULL while the report is pending.

Example

The rows must come back in the order shown.

LabResult

result_idpatient_idtest_namecollected_atreported_at
1301CBC2025-04-01 08:00:002025-04-01 09:00:00
2302CBC2025-04-01 08:30:002025-04-01 10:30:00
3303CBC2025-04-01 09:00:002025-04-01 10:30:00
4304Blood Culture2025-04-01 07:00:002025-04-03 07:00:00
5305Blood Culture2025-04-01 11:00:002025-04-04 11:00:00
6306Blood Culture2025-04-02 06:00:00NULL
7307HbA1c2025-04-02 10:00:002025-04-02 16:00:00

Output

result_idtest_nameturnaround_minutes
5Blood Culture4320
2CBC120
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.

Lab Tests Slower Than Their Usual Turnaround — SQL Problem

Different tests take different times — a CBC is quick, a blood culture is not — so the lab compares each report with the average turnaround of the same test.

  • Difficulty: Medium
  • Topics: Subqueries, Dates
  • Dialect: MySQL
  • Problem: #211

Problem statement

Different tests take different times — a CBC is quick, a blood culture is not — so the lab compares each report with the average turnaround of the same test. Turnaround is the whole minutes from collected_at to reported_at; reports still pending (reported_at NULL) are ignored everywhere, including in the averages.

Return result_id, test_name and turnaround_minutes of every report whose turnaround is strictly greater than its test's average turnaround. Order by test_name, then result_id.

Tables

Table: LabResult

ColumnType
result_idint
patient_idint
test_namevarchar
collected_atdatetime
reported_atdatetime

Primary key: result_id.

One row per test run. Times are recorded to the minute; reported_at is NULL while the report is pending.

Examples

Example 1

LabResult

result_idpatient_idtest_namecollected_atreported_at
1301CBC2025-04-01 08:00:002025-04-01 09:00:00
2302CBC2025-04-01 08:30:002025-04-01 10:30:00
3303CBC2025-04-01 09:00:002025-04-01 10:30:00
4304Blood Culture2025-04-01 07:00:002025-04-03 07:00:00
5305Blood Culture2025-04-01 11:00:002025-04-04 11:00:00
6306Blood Culture2025-04-02 06:00:00NULL
7307HbA1c2025-04-02 10:00:002025-04-02 16:00:00

Output

result_idtest_nameturnaround_minutes
5Blood Culture4320
2CBC120

How to solve Lab Tests Slower Than Their Usual Turnaround

The comparison value differs per test, so it cannot be one constant. A correlated subquery computes it for each row: the average of TIMESTAMPDIFF(MINUTE, collected_at, reported_at) over the rows with the same test_name. The outer query keeps a report when its own turnaround is strictly above that average.

Pending reports have a NULL turnaround. AVG skips NULLs automatically, so they never distort the average, and a NULL turnaround in the outer comparison is unknown, so they never appear — the explicit reported_at IS NOT NULL simply documents the rule. A report exactly equal to the average is not slower.

The window version computes AVG(...) OVER (PARTITION BY test_name) once per partition instead of once per row, which is cheaper on a large table. A third approach joins to a grouped table of totals and counts and compares minutes × count > total, which avoids fractions altogether.

Reference solution (MySQL)

SELECT r.result_id, r.test_name,
       TIMESTAMPDIFF(MINUTE, r.collected_at, r.reported_at) AS turnaround_minutes
FROM LabResult r
WHERE r.reported_at IS NOT NULL
  AND TIMESTAMPDIFF(MINUTE, r.collected_at, r.reported_at) > (
        SELECT AVG(TIMESTAMPDIFF(MINUTE, x.collected_at, x.reported_at))
        FROM LabResult x
        WHERE x.test_name = r.test_name AND x.reported_at IS NOT NULL
      )
ORDER BY r.test_name, r.result_id

Another way

SELECT result_id, test_name, tat AS turnaround_minutes FROM (
  SELECT result_id, test_name, TIMESTAMPDIFF(MINUTE, collected_at, reported_at) AS tat,
         AVG(TIMESTAMPDIFF(MINUTE, collected_at, reported_at)) OVER (PARTITION BY test_name) AS avg_tat
  FROM LabResult WHERE reported_at IS NOT NULL
) t WHERE tat > avg_tat ORDER BY test_name, result_id

Another way

SELECT r.result_id, r.test_name, TIMESTAMPDIFF(MINUTE, r.collected_at, r.reported_at) AS turnaround_minutes
FROM LabResult r
JOIN (SELECT test_name, SUM(TIMESTAMPDIFF(MINUTE, collected_at, reported_at)) AS total, COUNT(*) AS n FROM LabResult WHERE reported_at IS NOT NULL GROUP BY test_name) a
  ON a.test_name = r.test_name
WHERE r.reported_at IS NOT NULL AND TIMESTAMPDIFF(MINUTE, r.collected_at, r.reported_at) * a.n > a.total
ORDER BY r.test_name, r.result_id

← Patients Seen Across Several Specialties · Ward Bed Occupancy on the Census Date →