Lab Results — Abnormal Value Detection and Patient Trending

Laboratory data is one of the richest clinical signals. This lesson leverages the is_abnormal generated column, window functions for consecutive abnormal results, and critical-value detection.

1. Abnormal Rate by Test Type

The is_abnormal generated column (value_numeric < ref_low OR value_numeric > ref_high) lets you aggregate without repeating the logic:

SELECT
    test_name,
    COUNT(*)                                          AS total_results,
    COUNT(*) FILTER (WHERE is_abnormal)               AS abnormal_count,
    ROUND(100.0 * COUNT(*) FILTER (WHERE is_abnormal)
          / NULLIF(COUNT(*), 0), 1)                   AS abnormal_pct,
    ROUND(AVG(value_numeric), 2)                      AS mean_value,
    ROUND(MIN(value_numeric), 2)                      AS min_value,
    ROUND(MAX(value_numeric), 2)                      AS max_value
FROM hc_lab_results
GROUP BY test_name
ORDER BY abnormal_pct DESC;

What This Returns

test_name    | total_results | abnormal_count | abnormal_pct | mean_value
-------------+---------------+----------------+--------------+-----------
Troponin     |           150 |             95 |         63.3 |      0.031
BNP          |           150 |             95 |         63.3 |    150.000
Haemoglobin  |           150 |             59 |         39.3 |     11.200
...

2. Patients with 3+ Consecutive Abnormal Results

Use the LAG window function to detect runs of abnormal results for the same test:

WITH flagged AS (
    SELECT
        patient_id,
        test_name,
        collected_at,
        is_abnormal,
        LAG(is_abnormal, 1) OVER w AS prev1,
        LAG(is_abnormal, 2) OVER w AS prev2
    FROM hc_lab_results
    WHERE value_numeric IS NOT NULL
    WINDOW w AS (PARTITION BY patient_id, test_name ORDER BY collected_at)
)
SELECT DISTINCT
    lr.patient_id,
    p.name,
    p.mrn,
    f.test_name,
    COUNT(*) AS consecutive_abnormal_runs
FROM flagged f
JOIN hc_patients p ON p.id = f.patient_id
JOIN hc_lab_results lr
    ON lr.patient_id = f.patient_id AND lr.test_name = f.test_name
WHERE f.is_abnormal = true
  AND f.prev1 = true
  AND f.prev2 = true
GROUP BY lr.patient_id, p.name, p.mrn, f.test_name
ORDER BY consecutive_abnormal_runs DESC
LIMIT 20;

3. Critical Values — Extreme Outliers

Critical values (e.g., glucose > 3× upper limit or < lower/3) require immediate clinical action:

SELECT
    lr.patient_id,
    p.name,
    p.mrn,
    lr.test_name,
    lr.value_numeric,
    lr.ref_low,
    lr.ref_high,
    lr.unit,
    lr.collected_at,
    CASE
        WHEN lr.value_numeric > lr.ref_high * 3  THEN 'CRITICALLY HIGH'
        WHEN lr.ref_low > 0
             AND lr.value_numeric < lr.ref_low / 3 THEN 'CRITICALLY LOW'
    END AS critical_flag
FROM hc_lab_results lr
JOIN hc_patients p ON p.id = lr.patient_id
WHERE lr.value_numeric IS NOT NULL
  AND lr.ref_high IS NOT NULL
  AND lr.ref_low  IS NOT NULL
  AND (
        lr.value_numeric > lr.ref_high * 3
        OR (lr.ref_low > 0 AND lr.value_numeric < lr.ref_low / 3)
      )
ORDER BY lr.collected_at DESC;

What This Returns

patient_id | name       | test_name  | value_numeric | ref_high | critical_flag
-----------+------------+------------+---------------+----------+---------------
       241 | Patient 241| Troponin   |         0.144 |     0.04 | CRITICALLY HIGH
        11 | Patient 11 | BNP        |       600.000 |   100.00 | CRITICALLY HIGH
...

4. Per-Patient Lab Trend Over Time

Track how a specific metric evolves for a patient across admissions:

SELECT
    lr.patient_id,
    p.name,
    lr.test_name,
    lr.collected_at::DATE AS collected_date,
    lr.value_numeric,
    lr.ref_low,
    lr.ref_high,
    lr.is_abnormal,
    ROUND(AVG(lr.value_numeric) OVER (
        PARTITION BY lr.patient_id, lr.test_name
        ORDER BY lr.collected_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_3_avg
FROM hc_lab_results lr
JOIN hc_patients p ON p.id = lr.patient_id
WHERE lr.patient_id = 1
ORDER BY lr.test_name, lr.collected_at;

5. Abnormal Lab Load by Ward

Understand which wards generate the most abnormal results (proxy for patient acuity):

SELECT
    w.name                                            AS ward,
    COUNT(lr.id)                                      AS total_labs,
    COUNT(lr.id) FILTER (WHERE lr.is_abnormal)        AS abnormal_labs,
    ROUND(100.0 * COUNT(lr.id) FILTER (WHERE lr.is_abnormal)
          / NULLIF(COUNT(lr.id), 0), 1)               AS abnormal_pct
FROM hc_lab_results lr
JOIN hc_admissions a ON a.id = lr.admission_id
JOIN hc_wards w      ON w.id = a.ward_id
GROUP BY w.name
ORDER BY abnormal_pct DESC;

Key Takeaway

Generated columns like is_abnormal let you store derived flags efficiently and use them in FILTER, WHERE, and index predicates without repeating logic. The LAG(..., N) OVER (PARTITION BY ... ORDER BY ...) pattern is the standard tool for detecting consecutive states — consecutive abnormal results, consecutive payment failures, or consecutive login failures.