## Appendix A Prompt Structure
### Section [Schema] for Ablation Study  
The following schema defines the database structure used in query generation:  
-- demographic - patient information, count by demographic.subject_id CREATE TABLE 'demographic'' ( 'SUBJECT_ID\" INTERGER, 'HADM_ID\" INTERGER, 'NAME'')'TEXT, 'MarkITAL_STATUS''TEXT, 'AGE''INTEGER, 'DOB''TEXT, 'GENDER''TEXT, 'Language'TEXT, 'RELIGION''TEXT, 'ADMISSION_TYPE')'TEXT, 'DAYS_STAY'INTEGER, 'INSUANCE')'TEXT, 'ETHNICITY'TEXT, 'EXPIRE_FLAG'INTEGER, 'ADMISSION_LOCATION''TEXT, 'DIAGNOSIS'TEXT, 'DOD''TEXT, 'DOB_YEAR''INTEGER, 'DOD_YEAR''INTEGER, 'ADMITIME''TEXT, 'DISCHIME''TEXT, 'ADMITYPEAR''INTEGER );  
-- diagnoses: patient diagnoses, use for queries related -- to patient conditions CREATE TABLE ''diagnoses'' ( ''SUBJECT_ID'' INTEGER, ''HADM_ID'' INTEGER, ''ICD9_CODE'' TEXT, ''SHORT_TITLE'' TEXT, ''LONG_TITLE'' TEXT ); -- lab: patient lab results CREATE TABLE ''lab'' ( ''SUBJECT_ID'' INTEGER, ''HEAD_ID'' INTEGER, ''ITEMID'' INTEGER, ''CHARTTIME'' TEXT, ''FLAG'' TEXT, ''VALUE_UNIT'' TEXT, ''LABEL'' TEXT, ''FLUID'' TEXT, ''CATEGORY'' TEXT ); -- prescriptions: patient prescriptions CREATE TABLE ''prescriptions'' ( ''SUBJECT_ID'' INTEGER, ''HADM_ID'' INTEGER, ''ICUSTAY_ID'' REAL, ''DRUG_TYPE'' TEXT, ''DRUG'' TEXT, ''FORMULARY_DRUG_CD'' TEXT, ''ROUTE'' TEXT, ''DRUG_DOSE'' TEXT ); -- procedures: medical procedures performed on patients CREATE TABLE ''procedures'' ( ''SUBJECT_ID'' INTEGER, ''HADM_ID'' INTEGER, ''ICD9_CODE'' INTEGER, ''SHORT_TITLE'' TEXT, ''LONG_TITLE'' TEXT ); ```
_Appendix A.3. Section [Data_Preview] for Ablation Study_
``` SUBJECT_ID,HADM_ID,NAME,MATITAL_STATUS,AGE,DOB,GENDER,LANGUAGE, RELIGION,ADMISSION_TYPE,DAYS_STAY,INSUNANCE,ETHNICITY,EXPIRE_FLAG, ADMISSION_LOCATION,DISCHARGE_LOCATION,DIAGNOSIS,DOD,DOB_YEAR,DOD_YEAR, ADMITTIME,DISCHTIME,ADMITTYEAR 10026,132345,Jane [PERSON],,CATHOLIC,



SELECT DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\" FROM DEMOGRAPHIC INNER JOIN LAB ON DEMOGRAPHIC.\"SUBJECT_ID\" = LAB.\"SUBJECT_ID\" WHERE DEMOGRAPHIC.\"SUBJECT_ID\" = 18372
* **Gemini 1.5-pro**SELECT DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\" FROM DEMOGRAPHIC INNER JOIN LAB ON DEMOGRAPHIC.\"SUBJECT_ID\" = LAB.\"SUBJECT_ID\" WHERE DEMOGRAPHIC.\"SUBJECT_ID\" = 18372
* **Qwen-2.5-72B**:SELECT \"demographic\".\"DAYS_STAY\", \"lab\".\"CATEGORY\" FROM \"demographic\" INNER JOIN \"lab\" ON \"demographic\".\"HADM_ID\" = \"lab\".\"HADM_ID\" WHERE \"demographic\".\"SUBJECT_ID\" = 18372
* **Claude-3.5**SELECT DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\" FROM DEMOGRAPHIC LEFT JOIN LAB ON DEMOGRAPHIC.\"SUBJECT_ID\" = LAB.\"SUBJECT_ID\" WHERE DEMOGRAPHIC.\"SUBJECT_ID\" = 18372 GROUP BY DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\"
* **GPT-4o**SELECT DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\" FROM DEMOGRAPHIC INNER JOIN LAB ON DEMOGRAPHIC.HADM_ID = LAB.HADM_ID WHERE DEMOGRAPHIC.\"SUBJECT_ID\" = 18372
* **GPT-4o**-mini**SELECT DEMOGRAPHIC.\"DAYS_STAY\", LAB.\"CATEGORY\" FROM DEMOGRAPHIC LEFT JOIN LAB ON DEMOGRAPHIC.\"HADM_ID\" = LAB.\"HADM_ID\" WHERE DEMOGRAPHIC.\"SUBJECT_ID\" = 18372

_Appendix B.2. Case Number: a90 ecf2 fa6 bc7c4 af1d8 fc44 eb09a881_

**Question**: Find the number of patients who are married and have been diagnosed with poleonephritis, unspecified. **Ground Truth SQL**:SELECT COUNT ( DISTINCT DEMOGRAPHIC.\"SUBJECT_ID\" ) FROM DEMOGRAPHIC INNER JOIN DIAGNOSES ON DEMOGRAPHIC.HADM_ID = DIAGNOSES.HADM_ID WHERE DEMOGRAPHIC.\"HARTIAL_STATUS\" = \"MARRIED\" AND DIAGNOSES.\"LONG_TITLE\" = \"Pyelonephritis, unspecified\"

**Model Responses:**

* **Llama3.3-70b**:SELECT COUNT(DISTINCT DEMOGRAPHIC. \"SUBJECT_ID\") FROM DEMOGRAPHIC INNER JOIN DIAGNOSES ON DEMOGRAPHIC. \"HADM_ID\" = DIAGNOSES. \"HADM_ID\" = WHERE LOWER(DEMOGRAPHIC. \"MARITAL_STATUS\") ='married' AND LOWER(DIAGNOSES. \"SHORT_TITLE\") = 'pyelonephritis, unspecified' ```
* **Mixtral-8x22b**: SELECT COUNT(DISTINCT DEMOGRAPHIC. \"SUBJECT_ID\") FROM DEMOGRAPHIC INNER JOIN DIAGNOSES ON DEMOGRAPHIC. \"SUBJECT_ID\" = DIAGNOSES. \"SUBJECT_ID\" = WHERE LOWER(DEMOGRAPHIC. \"MARITAL_STATUS\") ='married' AND LOWER(DIAGNOSES.