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
| Column | Type |
|---|---|
| result_id | int |
| patient_id | int |
| test_name | varchar |
| collected_at | datetime |
| reported_at | datetime |
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_id | patient_id | test_name | collected_at | reported_at |
|---|---|---|---|---|
| 1 | 301 | CBC | 2025-04-01 08:00:00 | 2025-04-01 09:00:00 |
| 2 | 302 | CBC | 2025-04-01 08:30:00 | 2025-04-01 10:30:00 |
| 3 | 303 | CBC | 2025-04-01 09:00:00 | 2025-04-01 10:30:00 |
| 4 | 304 | Blood Culture | 2025-04-01 07:00:00 | 2025-04-03 07:00:00 |
| 5 | 305 | Blood Culture | 2025-04-01 11:00:00 | 2025-04-04 11:00:00 |
| 6 | 306 | Blood Culture | 2025-04-02 06:00:00 | NULL |
| 7 | 307 | HbA1c | 2025-04-02 10:00:00 | 2025-04-02 16:00:00 |
Output
| result_id | test_name | turnaround_minutes |
|---|---|---|
| 5 | Blood Culture | 4320 |
| 2 | CBC | 120 |
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_idAnother 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_idAnother 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 →