CREATE TABLE labevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, itemid NUMBER, charttime TIME, valuenum NUMBER, valueuom CLOB)
CREATE TABLE d_icd_procedures (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)
CREATE TABLE diagnoses_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE d_icd_diagnoses (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)
CREATE TABLE inputevents_cv (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, amount NUMBER)
CREATE TABLE cost (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, event_type CLOB, event_id NUMBER, chargetime TIME, cost NUMBER)
CREATE TABLE prescriptions (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, startdate TIME, enddate TIME, drug CLOB, dose_val_rx CLOB, dose_unit_rx CLOB, route CLOB)
CREATE TABLE icustays (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, first_careunit CLOB, last_careunit CLOB, first_wardid NUMBER, last_wardid NUMBER, intime TIME, outtime TIME)
CREATE TABLE outputevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, value NUMBER)
CREATE TABLE admissions (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, admittime TIME, dischtime TIME, admission_type CLOB, admission_location CLOB, discharge_location CLOB, insurance CLOB, language CLOB, marital_status CLOB, ethnicity CLOB, age NUMBER)
CREATE TABLE procedures_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE patients (row_id NUMBER, subject_id NUMBER, gender CLOB, dob TIME, dod TIME)
CREATE TABLE d_items (row_id NUMBER, itemid NUMBER, label CLOB, linksto CLOB)
CREATE TABLE transfers (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, eventtype CLOB, careunit CLOB, wardid NUMBER, intime TIME, outtime TIME)
CREATE TABLE chartevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, itemid NUMBER, charttime TIME, valuenum NUMBER, valueuom CLOB)
CREATE TABLE d_labitems (row_id NUMBER, itemid NUMBER, label CLOB)
CREATE TABLE microbiologyevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, charttime TIME, spec_type_desc CLOB, org_name CLOB)

count the number of patients who were diagnosed with asphyxiation/strangulat and who didn't come back to the hospital in the same month in the last year.
SELECT (SELECT COUNT(DISTINCT t1.subject_id) FROM (SELECT admissions.subject_id, diagnoses_icd.charttime FROM diagnoses_icd JOIN admissions ON diagnoses_icd.hadm_id = admissions.hadm_id WHERE diagnoses_icd.icd9_code = (SELECT d_icd_diagnoses.icd9_code FROM d_icd_diagnoses WHERE d_icd_diagnoses.short_title = 'asphyxiation/strangulat') AND DATETIME(diagnoses_icd.charttime, 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year')) AS t1) - (SELECT COUNT(DISTINCT t2.subject_id) FROM (SELECT admissions.subject_id, diagnoses_icd.charttime FROM diagnoses_icd JOIN admissions ON diagnoses_icd.hadm_id = admissions.hadm_id WHERE diagnoses_icd.icd9_code = (SELECT d_icd_diagnoses.icd9_code FROM d_icd_diagnoses WHERE d_icd_diagnoses.short_title = 'asphyxiation/strangulat') AND DATETIME(diagnoses_icd.charttime, 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year')) AS t2 JOIN admissions ON t2.subject_id = admissions.subject_id WHERE t2.charttime < admissions.admittime AND DATETIME(admissions.admittime, 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year') AND DATETIME(t2.charttime, 'start of month') = DATETIME(admissions.admittime, 'start of month'))
WITH "_u_0" AS (SELECT "d_icd_diagnoses"."icd9_code" FROM "d_icd_diagnoses" WHERE "d_icd_diagnoses"."short_title" = 'asphyxiation/strangulat') SELECT (SELECT COUNT(DISTINCT "admissions"."subject_id") FROM "diagnoses_icd" JOIN "_u_0" "_u_0" ON "_u_0"."icd9_code" = "diagnoses_icd"."icd9_code" JOIN "admissions" ON "admissions"."hadm_id" = "diagnoses_icd"."hadm_id" WHERE DATETIME("diagnoses_icd"."charttime", 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year')) - (SELECT COUNT(DISTINCT "admissions_2"."subject_id") FROM "diagnoses_icd" JOIN "_u_0" "_u_2" ON "_u_2"."icd9_code" = "diagnoses_icd"."icd9_code" JOIN "admissions" "admissions_2" ON "admissions_2"."hadm_id" = "diagnoses_icd"."hadm_id" JOIN "admissions" ON "admissions"."admittime" > "diagnoses_icd"."charttime" AND "admissions"."subject_id" = "admissions_2"."subject_id" AND DATETIME("admissions"."admittime", 'start of month') = DATETIME("diagnoses_icd"."charttime", 'start of month') AND DATETIME("admissions"."admittime", 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year') WHERE DATETIME("diagnoses_icd"."charttime", 'start of year') = DATETIME(CURRENT_TIME(), 'start of year', '-1 year'))