Mimic Iv Patient Analysis

MIMIC-IV Patient Analysis: Comprehensive QA Generation

Goal

Systematically explore a patient's complete clinical record and submit diverse, high-quality QA pairs covering all meaningful clinical domains. Target 20–30 QA pairs for complex patients with multiple admissions; 10–15 for simple cases.

Database Overview

27 tables with two prefixes:

• hosp_ — hospital-level data (diagnoses, procedures, medications, admissions, labs)
• icu_ — ICU-specific data (stays, inputs/outputs, procedures, events)

Three metadata tables: table_comments, column_comments, column_documentation

Start with get_database_info to confirm available tables.

Key Column Names (Common Pitfalls)

Incorrect column names are the #1 cause of failed queries.

Critical drug column distinction: hosp_prescriptions uses drug (orders). hosp_pharmacy and hosp_emar use medication (dispensed/administered). Using medication in hosp_prescriptions will always fail.

Critical: hosp_labevents does NOT exist. Use hosp_omr for outpatient measurements. Use icu_inputevents/icu_outputevents for ICU lab-like data.

Core JOIN Patterns

-- hosp_prescriptions: drug frequency across all admissions
SELECT drug, COUNT(*) as cnt FROM hosp_prescriptions
WHERE hadm_id IN (SELECT hadm_id FROM hosp_admissions WHERE subject_id = <sid>)
GROUP BY drug ORDER BY cnt DESC LIMIT 20

-- hosp_pharmacy: dispensed medications (uses medication, not drug; no subject_id column)
SELECT medication, route, frequency, COUNT(*) as cnt
FROM hosp_pharmacy
WHERE hadm_id IN (SELECT hadm_id FROM hosp_admissions WHERE subject_id = <sid>)
GROUP BY medication ORDER BY cnt DESC LIMIT 20

-- hosp_omr: query directly by subject_id (outpatient measurements)
SELECT chartdate, result_name, result_value
FROM hosp_omr WHERE subject_id = <sid> ORDER BY chartdate

-- ICD diagnosis with readable title
SELECT d.hadm_id, d.seq_num, d.icd_code, d.icd_version, dt.long_title
FROM hosp_diagnoses_icd d
JOIN hosp_d_icd_diagnoses dt ON d.icd_code = dt.icd_code AND d.icd_version = dt.icd_version
WHERE d.subject_id = <sid>

-- Tables with hadm_id only (no subject_id): JOIN through hosp_admissions
SELECT ... FROM hosp_services s
JOIN hosp_admissions ha ON s.hadm_id = ha.hadm_id
WHERE ha.subject_id = <sid>

-- ICU stays: JOIN through hosp_admissions
SELECT ic.stay_id, ic.hadm_id, ic.intime, ic.outtime, ic.los, ic.first_careunit
FROM icu_icustays ic
JOIN hosp_admissions ha ON ic.hadm_id = ha.hadm_id
WHERE ha.subject_id = <sid>

-- ICU inputs aggregated (total per medication)
SELECT di.label, SUM(ie.amount) as total, ie.amountuom
FROM icu_inputevents ie JOIN icu_d_items di ON ie.itemid = di.itemid
WHERE ie.stay_id = <stay_id> GROUP BY di.label, ie.amountuom ORDER BY total DESC

-- ICU outputs (urine, drainage)
SELECT di.label, SUM(oe.value) as total, oe.valueuom
FROM icu_outputevents oe JOIN icu_d_items di ON oe.itemid = di.itemid
WHERE oe.stay_id = <stay_id> GROUP BY di.label, oe.valueuom

-- ICU procedures (ventilation, dialysis): duration in minutes
SELECT di.label, SUM(pe.value) as total_minutes, pe.valueuom
FROM icu_procedureevents pe JOIN icu_d_items di ON pe.itemid = di.itemid
WHERE pe.stay_id = <stay_id> GROUP BY di.label, pe.valueuom

When a query fails with "no such column", check column_comments:

SELECT column_name, comment FROM column_comments WHERE table_name = '<table>'

Systematic Exploration Order

Phase 1 — Foundation (always first)

1. Patient demographics: hosp_patients → age, gender, date of death
2. Admissions overview: hosp_admissions → count, dates, admission types, insurance, discharge locations, in-hospital deaths. For many admissions, query total count first, then fetch in batches.
3. Diagnoses: hosp_diagnoses_icd JOIN hosp_d_icd_diagnoses → primary and comorbid conditions. Use OFFSET to paginate if results are capped.
4. Procedures: hosp_procedures_icd JOIN hosp_d_icd_procedures → surgical and clinical interventions
5. ICU stays: icu_icustays (JOIN through hosp_admissions) → LOS, care units, timing

Phase 2 — Care Context

6. Clinical services: hosp_services → service transitions per admission
7. Prescriptions (ordered): hosp_prescriptions → GROUP BY drug ORDER BY COUNT(*) DESC for most-ordered drugs. Use drug column, not medication.
8. Pharmacy (dispensed): hosp_pharmacy → GROUP BY medication ORDER BY COUNT(*) DESC for most-dispensed drugs with route/frequency detail. This complements prescriptions and is often more clinically specific.
9. DRG classifications: hosp_drgcodes → billing severity and mortality risk (description is inline, no JOIN needed)
10. Transfers: hosp_transfers → intra-hospital care unit movement sequences
11. Microbiology: hosp_microbiologyevents → organisms, antibiotic sensitivities (always include ab_name and interpretation columns for resistance patterns)

Phase 3 — Clinical Depth (when ICU stays exist, do steps 12–13; otherwise pursue as relevant)

12. ICU inputs/outputs: For each ICU stay, query icu_inputevents and icu_outputevents by stay_id → aggregate (GROUP BY di.label, SUM(amount)) to identify key medications, vasopressors, fluid totals, and urine output. For extended stays (>5 days), also check icu_ingredientevents for nutritional formula totals.
13. ICU procedures: icu_procedureevents → ventilation duration (sum of minutes), dialysis
14. Outpatient measurements: hosp_omr → weight, BMI, blood pressure trends over time
15. eMAR: hosp_emar → actual medication administrations with GROUP BY medication, event_txt ORDER BY COUNT(*) DESC
16. Provider orders: hosp_poe → COUNT(*) GROUP BY order_type for order distribution
17. HCPCS events: hosp_hcpcsevents → billed services/procedures

Phase 4 — Synthesis

18. Identify clinically interesting patterns: readmission intervals (days between discharge and next admission), disease progression, care escalation over time, discharge destination evolution, per-admission diagnosis complexity (diagnoses count per hadm_id)
19. Look for cross-cutting themes: recurrent infections with same/different organisms, resistance evolution, DRG severity trajectory, ICU readmissions

Aggregation tip: When a table returns truncated results, use COUNT(*) first, then GROUP BY for summary, and OFFSET to paginate. Prefer compact aggregate queries over many sequential offset queries.

QA Generation Strategy

Coverage Targets

Generate QA pairs across these domains — focus on what's clinically rich for this patient.

QA Quality Standards

Strong QA pairs include:

• Concrete values: Exact dates, drug names with doses/totals (e.g., "Heparin 69,193 units"), organism names with full resistance patterns, LOS in days, procedure names with laterality
• Clinical context: Not just the fact but why it matters (e.g., "discharged to rehab, indicating functional impairment")
• Completeness: Full enumeration when there are only a few items (e.g., list all 4 ICU stays with dates and durations); summaries with top items when there are many
• Cross-cutting synthesis: Connecting data from multiple tables or admissions into a coherent narrative

Anti-patterns to avoid:

• Vague counts without specifics: "19 prescription orders were placed" → instead name the top drugs with counts
• Trivial single-fact answers: "The patient is a 76-year-old male on Medicare" → embed demographics into richer clinical context
• Redundant pairs covering the same information in slightly different wording
• Schema questions ("What columns does this table have?")

Example: weak vs. strong

• Weak: "What were the prescriptions for this patient?" → "19 prescription orders were placed, including both MAIN and BASE drug types."
• Strong: "What were the most frequently prescribed medications?" → "Insulin (65 prescriptions), Furosemide (34), Warfarin (16), Aspirin (15), Levothyroxine (13) — reflecting diabetes, heart failure, and thyroid management."

High-Value QA Types

These patterns tend to produce rich, specific QA pairs:

1. ICU medication details: "What vasopressors/sedatives/antibiotics were used during [ICU stay] and in what total amounts?" (requires icu_inputevents aggregation)
2. Antibiotic resistance patterns: "What organisms were identified and what was the resistance/sensitivity pattern per organism?" (requires hosp_microbiologyevents with ab_name + interpretation)
3. Longitudinal trajectory: "How did discharge destinations change over [N] years?" or "What was the pattern of care escalation?"
4. Readmission intervals: "What were the shortest intervals between discharge and subsequent readmission, and what were the associated conditions?"
5. Specific procedural detail: "What specific approach was used for [procedure] and what was the clinical indication?"
6. Drug frequency across all admissions: "What were the top 10 most prescribed/dispensed medications across all admissions?" (requires both hosp_prescriptions + hosp_pharmacy)
7. DRG severity evolution: "How did DRG severity and mortality scores change over successive admissions?"
8. Care unit progression during complex admission: "What care units did the patient transit through during their longest hospitalization and in what order?"
9. Fluid balance during ICU: "What were the total inputs and outputs during [ICU stay]?" (requires icu_inputevents + icu_outputevents)
10. Advance care planning: "When was DNR status first documented and how consistently was it recorded?"
11. Admission pattern analysis: "What was the distribution of admission types, sources, and frequency over the observation period?"
12. Per-admission complexity: "Which admissions had the most diagnoses and what conditions drove their complexity?"

Submission Pattern

Submit QA pairs in thematic batches after completing each exploration phase — don't submit one at a time. Interleave: explore a domain → verify data quality → submit 3–6 related QA pairs → continue. This ensures progress is saved and helps maintain thematic coherence in QA pairs.

Handling Query Failures

When a query fails:

1. Read the error — it often lists the available columns for that table
2. Correct the column name using the table above and retry once
3. If still failing, check column_comments for the correct schema
4. If a table doesn't exist, use the alternatives listed above

Do not spend more than 2 retries on any single query — move on if data isn't available.