PaperlayerView exact revision on arXiv

Source-linked AI summary

Patient-Similarity Cohort Reasoning in Clinical Text-to-SQL

Yifei Shen, Yilun Zhao, Justice Ou, Tinglin Huang, Arman Cohan

arXiv:2601.09876v1cs.CL

TL;DR

Clinical text-to-SQL requires executable reasoning over heterogeneous EHR tables, temporal windows, coding systems, and patient-similarity cohorts, exposing limits of general-domain benchmarks. CLINSQL addresses this gap with a 633-task MIMIC-IV v3.1 benchmark, rubric-based evaluation, and model testing; results show persistent difficulty, while schema hints yield gains. Its scope is limited by reliance on one health system and SQL environment and by substantial expert-curation requirements.

  • Problem

    Real-world clinical text-to-SQL requires specialized terminology, temporal reasoning, heterogeneous-table integration, and patient-similarity cohort reasoning beyond traditional benchmarks.

  • Method

    CLINSQL provides 633 expert-annotated MIMIC-IV v3.1 tasks with multi-table, temporally aware SQL, rubric-based validation, and evaluation of 22 models under Chain-of-Thought self-refinement.

  • Results

    Performance remains challenging: GPT-5-mini reaches 74.7% test execution, Gemini-2.5-Pro falls from 85.5% on Easy to 67.2% on Hard, and schema hints yield consistent gains, especially on medium and hard cases.

  • Takeaways & Limitations

    CLINSQL establishes a rigorous, domain-grounded target for advancing trustworthy clinical text-to-SQL and EHR analytics.

  • Takeaways & Limitations

    The benchmark uses data from one health system and one SQL environment and requires substantial expert involvement, limiting transferability and scalability.

Abstract

from arXiv · show

Real-world clinical text-to-SQL requires reasoning over heterogeneous EHR tables, temporal windows, and patient-similarity cohorts to produce executable queries. We introduce CLINSQL, a benchmark of 633 expert-annotated tasks on MIMIC-IV v3.1 that demands multi-table joins, clinically meaningful filters, and executable SQL. Solving CLINSQL entails navigating schema metadata and clinical coding systems, handling long contexts, and composing multi-step queries beyond traditional text-to-SQL. We evaluate 22 proprietary and open-source models under Chain-of-Thought self-refinement and use rubric-based SQL analysis with execution checks that prioritize critical clinical requirements. Despite recent advances, performance remains far from clinical reliability: on the test set, GPT-5-mini attains 74.7% execution score, DeepSeek-R1 leads open-source at 69.2% and Gemini-2.5-Pro drops from 85.5% on Easy to 67.2% on Hard. Progress on CLINSQL marks tangible advances toward clinically reliable text-to-SQL for real-world EHR analytics.

1 Introduction

CLINSQL targets clinical text-to-SQL beyond general-domain parsing by requiring temporal, multi-table, coding, and patient-similarity reasoning. It benchmarks models with rubric-based execution evaluation and finds substantial difficulty, especially on harder tasks.

  • Clinical text-to-SQL must handle specialized terminology, fine-grained temporal reasoning across heterogeneous EHR tables, and cohort-level clinical reasoning.
  • Patient-similarity queries compare similar patients under clinically meaningful constraints rather than performing only point retrieval.
  • CLINSQL contains 633 expert-annotated tasks on MIMIC-IV v3.1, spanning six realistic scenario types with multi-table, temporally aware SQL and patient-similarity cohort construction.
  • The benchmark evaluates 22 proprietary and open-source models using Chain-of-Thought self-refinement, rubric-based analysis, and execution checks for critical clinical requirements.
  • 85.5% execution on Easy fell to 67.2% on Hard for Gemini-2.5-Pro, while GPT-5-mini reached 74.7% overall test execution and DeepSeek-R1 reached 69.1%.
  • Common failures involved cohort specification drift, schema or output mismatches, and mis-specified clinical aggregations; schema hints produced consistent gains, especially on medium and hard cases.

2 Related Work

Clinical text-to-SQL benchmarks extend beyond general-domain parsing because healthcare queries require medical terminology, complex temporal relationships, and clinical reasoning. CLINSQL emphasizes patient-similarity cohort reasoning and stratified outcomes over realistic clinical data.

  • Healthcare text-to-SQL requires medical terminology, complex temporal relationships, and clinical reasoning beyond standard database operations.
  • Patient-similarity reasoning identifies cohorts using multidimensional similarity criteria rather than simple retrieval or aggregation.
  • Existing clinical benchmarks often retrieve specific patients with known identifiers, covering only a limited subset of clinical analysis scenarios.
  • CLINSQL requires models to define cohorts, apply temporal and phenotyping logic, and compute stratified cohort-level outcomes over MIMIC-IV v3.1.

3 Benchmark Construction

CLINSQL is built from realistic clinical scenarios requiring multi-table, temporally aware SQL and patient-similarity cohort construction. Its expert-authored rubric trees evaluate both query structure and executed clinical results.

  • CLINSQL uses six clinical scenario types grounded in realistic healthcare data analysis and clinical decision-making.
  • Annotators select representative MIMIC-IV patients across age, condition, utilization, and acuity dimensions before formulating physician-relevant questions.
  • Expert annotation maps clinical questions to required tables, columns, values, and relationships, then constructs executable gold-standard SQL with joins and temporal constraints.
  • Each question includes separate rubric trees for SQL quality and executed-result validation.
  • Rubric Design Principles: Rubrics decompose evaluation into binary leaf checks with critical nodes, sequential dependencies, and weighted non-critical scoring.
  • Expert validation reviews clinical relevance, terminology, SQL correctness, output plausibility, and evaluation coverage.

4 Evaluation Protocol

The evaluation protocol combines rubric-based SQL assessment with execution-level checks. It prioritizes critical clinical requirements while allowing clinically equivalent valid formulations.

  • SQL evaluation verifies cohort construction, clinical concept mapping, schema relationships, joins, types, grouping, aggregation, and task-specific interpretation.
  • Critical failures collapse parent scores, while non-critical checks contribute through weighted averaging after prerequisites are satisfied.
  • Execution evaluation first checks CSV presence, exact columns, null absence, and basic types, then assesses clinically grounded value ranges.
  • Plausible and acceptable value bands support equivalent answers while enforcing progressively tighter tolerances.

5 Experiment

The experiment evaluates 22 models on CLINSQL using SQL and execution scores under Chain-of-Thought prompting with self-refinement. Results show substantial difficulty, especially on Hard scenarios, alongside gains from reasoning prompts.

  • Experiment Setup: 22 proprietary and open-source models are evaluated with SQL and execution scores on CLINSQL.
  • Experiment Setup: Models return executable BigQuery SQL under Direct Output or Chain-of-Thought prompting, with up to two error-guided self-refinement rounds.
  • Main Findings: 18.24% is Gemini-2.5-Pro's execution-score drop from Easy to Hard.
  • Main Findings: 69.2% average test execution by DeepSeek-R1 surpasses several proprietary baselines, but remains about 5.5 points below GPT-5-mini.
  • Main Findings: Chain-of-Thought generally improves performance, although gains vary substantially across models.
  • Failure Analysis: 54% of analyzed failures involve cohort specification and coding, while 24% involve output schema and formatting.

6 Conclusion

CLINSQL targets trustworthy clinical text-to-SQL by combining realistic EHR challenges with rubric-based evaluation. Results indicate that current systems remain short of clinically reliable operation, with recurring cohort, schema, and aggregation errors.

  • CLINSQL captures heterogeneous tables, temporal windows, and patient-similarity cohort construction in realistic clinical text-to-SQL tasks.
  • The benchmark evaluates broad model coverage using domain-grounded rubric protocols and execution checks.
  • Performance remains short of clinically reliable operation, with errors clustering around cohort specification, schema or formatting, and aggregation or clinical statistics.

Limitations

CLINSQL advances clinically grounded text-to-SQL evaluation but remains limited in transferability and scalability. Its current scope is a single health system, SQL environment, and expert-intensive curation process.

  • Scope: CLINSQL uses MIMIC-IV v3.1 from a single health system and targets one SQL environment, limiting transferability across EHR ecosystems, data models, and database backends.These constraints define the benchmark’s current generalization boundary.
  • Curation burden: Expert involvement is required for scenario specification, gold-standard SQL authoring, and rubric-aligned leaf rationales.The curation process provides high-fidelity supervision but requires substantial domain expertise.
  • Curation burden: Training, annotation, and review demands reduce throughput and hinder scaling to substantially larger datasets without additional tooling or alternative supervision strategies.The limitation concerns the scalability of expert curation rather than the benchmark’s fidelity.

A CLINSQL Benchmark Construction

The supplied construction materials show expert annotation and clinically grounded benchmark examples. The examples span patient characteristics, intensive-care comparisons, and outcome-oriented query requirements.

  • Benchmark construction: Six expert annotators contributed to CLINSQL benchmark construction.Two annotators were paper authors, and their table rows omit year, major, and scenario details for confidentiality.
  • Clinical query examples: A sample query compares 81-year-old female Medicare patients with principal AMI after transfer, reporting readmission and length-of-stay statistics.The query specifies age, sex, insurance, transfer status, diagnosis codes, and a 30-day readmission outcome.
  • Clinical query examples: A sample ICU query compares male patients aged 55–65 receiving HFNC within 24 hours with condition-matched controls.Requested outputs include instability-score quantiles, vital-sign burdens, ICU length of stay, and mortality.

B.3 Laboratory Results Analysis Example

The examples cover laboratory-result categorization alongside medication duration, diagnostic intensity, and risk-stratified outcome analysis. Their requested outputs combine distributions, rates, comparisons, and lengths of stay.

  • Laboratory Results Analysis: The laboratory example classifies first hs-TnT among female ACS admissions aged 46–56 into Normal, Borderline, and Myocardial Injury groups.It requests counts, percentages, and mean hospital length of stay for each category.
  • Medication Management: The medication example asks for the interquartile range of single inpatient amiodarone prescription durations among females aged 59–69.The target measure is prescription duration in days.
  • Diagnostic Procedures: The diagnostic-intensity example stratifies first-72-hour procedures into quartiles for male patients aged 83–93 with sepsis on their first ICU stay.It requests mean procedure counts, mean ICU length of stay, and mortality by quartile.
  • Disease Diagnosis and Outcomes: The risk-stratification example assigns female inpatients aged 70–80 with pulmonary embolism to risk-score quintiles.It requests 90-day mortality, a general mortality comparison, AKI and ARDS rates, and median survivor length of stay per quintile.

C Error Analysis

The error-analysis materials organize failures around output schema and formatting, cohort specification and coding, and aggregation and clinical statistics. The experiments also compare prompting regimes and include a schema-hinted setup for GPT-5-mini validation.

  • Error Analysis: Error analysis includes a sample focused on output schema and formatting.The cited figure identifies this as a distinct error-analysis category.
  • Error Analysis: A separate sample addresses cohort specification and coding.This category concerns how the cohort and clinical coding requirements are handled.
  • Error Analysis: Another sample addresses aggregation and clinical statistics.The figure identifies this as a separate error-analysis focus.
  • Prompting setup: The experiments include exact prompt templates for Chain-of-Thought and Direct Output generation and refinement.The cited materials also identify schema-hinted Chain-of-Thought generation and refinement prompts.
  • Prompting setup: The schema-hinted validation setup runs GPT-5-mini across six clinical domains and easy, medium, and hard tiers, using up to two execution-driven refinements.Hints extracted from reference SQL and result headers instruct ICD filtering and expected SELECT aliases.
  • Comparative evaluation: Figures 21 and 22 compare full validation execution and SQL scores between Chain-of-Thought reasoning and Direct Output for all models.The figures provide separate comparisons for execution score and SQL score.

H Scenario-Level Results

CLINSQL reports scenario-level SQL and execution results across six clinical tasks, alongside checks of execution reliability, evaluator agreement, and benchmark construction quality.

  • Scenario-Level Results: Scenario-level results are reported separately for Demog., Vitals, Labs, Meds, Dx Proc., and Dx & Outc. on validation and test sets.Tables 8 and 9 provide SQL and execution scores for the six scenarios.
  • Execution Reliability: Execution reliability is evaluated across all scenarios, including queries that execute without errors and success by initial and refined attempts.Attempts A1, A2, and A3 represent the initial query and up to two refinements.
  • Human–GPT Agreement: 100 sampled examples are used to compare GPT-5 rubric decisions with two medically trained annotators at leaf-level and final pass/fail outcomes.Disagreements concentrate in borderline partial-credit cases and do not materially change relative model rankings.
  • Inter-Annotator Agreement: 92% exact SQL-level agreement rises to 98% when near-miss queries are accepted as equivalent alternatives.The agreement study uses 50 validation/test scenarios and compares independently reconstructed SQL queries after canonicalization and execution.
  • Inter-Annotator Agreement: 91% raw rubric agreement with Cohen’s κ = 0.82 yields 94% pass/fail agreement across 50 scenarios.The rubric comparison covers leaf presence and criticality, with critical-first aggregation used for final pass/fail outcomes.

M Reconciling Execution Passes with SQL Analysis Failures

CLINSQL compares SQL-analysis correctness with execution-based clinical plausibility and finds strong alignment, while sampled divergence cases expose incomplete cohort logic and temporal reasoning.

  • Score Reconciliation: SQL Score and Execution Score show strong model-level alignment, with Pearson r = 0.8597 and Spearman ρ = 0.8554.SQL analysis evaluates intended cohort logic, whereas execution scoring evaluates clinically plausible results consistent with the gold answer.
  • Divergence Analysis: About 95% of 40 sampled large-gap outliers have high execution scores but low SQL scores.The outlier study samples model–dataset points whose absolute score gap is at least 20 percentage points.
  • Divergence Analysis: These divergence cases reflect partial or imprecise cohort logic, including missing secondary exclusions, incomplete temporal logic, or incorrect aggregation.The finding shows why execution success alone may not capture whether a query fully encodes the intended clinical cohort.
  • Evaluation Design: The benchmark represents each task with a clinical scenario, natural-language question, gold SQL, executed results table, and evaluation guideline.Annotators construct the question, executable SQL, results, and criteria used to assess other models’ queries and outputs.
  • Evaluation Design: Tree-structured rubrics decompose evaluation into verifiable criteria spanning patient cohorts, medical concepts, database integration, and clinical analytics.Critical nodes can cause parent failure, while non-critical nodes contribute to partial scoring after critical requirements pass.
  • Evaluation Design: Sequential dependencies stop downstream evaluation when an earlier logical requirement fails, such as cohort filtering before aggregation assessment.The framework also assigns weights so clinically important components, such as patient cohort construction, have greater influence.
Loading 2601.09876v1…