CREATE TABLE patients (row_id NUMBER, subject_id NUMBER, gender CLOB, dob TIME, dod TIME)
CREATE TABLE inputevents_cv (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, amount NUMBER)
CREATE TABLE diagnoses_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE labevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, itemid NUMBER, charttime TIME, valuenum NUMBER, valueuom CLOB)
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 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 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)
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 outputevents (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icustay_id NUMBER, charttime TIME, itemid NUMBER, value NUMBER)
CREATE TABLE d_icd_procedures (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)
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 procedures_icd (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, icd9_code CLOB, charttime TIME)
CREATE TABLE d_items (row_id NUMBER, itemid NUMBER, label CLOB, linksto 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 cost (row_id NUMBER, subject_id NUMBER, hadm_id NUMBER, event_type CLOB, event_id NUMBER, chargetime TIME, cost NUMBER)
CREATE TABLE d_icd_diagnoses (row_id NUMBER, icd9_code CLOB, short_title CLOB, long_title CLOB)

calculate the four year survival rate among the patients who were prescribed with prasugrel after having been diagnosed with klebsiella pneumoniae.
SELECT SUM(CASE WHEN patients.dod IS NULL THEN 1 WHEN STRFTIME('%j', patients.dod) - STRFTIME('%j', t4.charttime) > 4 * 365 THEN 1 ELSE 0 END) * 100 / COUNT(*) FROM (SELECT t2.subject_id, t2.charttime FROM (SELECT t1.subject_id, t1.charttime 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 = 'klebsiella pneumoniae') GROUP BY admissions.subject_id HAVING MIN(diagnoses_icd.charttime) = diagnoses_icd.charttime) AS t1 WHERE STRFTIME('%j', CURRENT_TIME()) - STRFTIME('%j', t1.charttime) > 4 * 365) AS t2 JOIN (SELECT admissions.subject_id, prescriptions.startdate FROM prescriptions JOIN admissions ON prescriptions.hadm_id = admissions.hadm_id WHERE prescriptions.drug = 'prasugrel') AS t3 ON t2.subject_id = t3.subject_id WHERE t2.charttime < t3.startdate) AS t4 JOIN patients ON t4.subject_id = patients.subject_id
WITH "t1" AS (SELECT "admissions"."subject_id", "diagnoses_icd"."charttime" FROM "diagnoses_icd" JOIN "d_icd_diagnoses" ON "d_icd_diagnoses"."icd9_code" = "diagnoses_icd"."icd9_code" AND "d_icd_diagnoses"."short_title" = 'klebsiella pneumoniae' JOIN "admissions" ON "admissions"."hadm_id" = "diagnoses_icd"."hadm_id" GROUP BY "admissions"."subject_id" HAVING "diagnoses_icd"."charttime" = MIN("diagnoses_icd"."charttime")), "t3" AS (SELECT "admissions"."subject_id", "prescriptions"."startdate" FROM "prescriptions" JOIN "admissions" ON "admissions"."hadm_id" = "prescriptions"."hadm_id" WHERE "prescriptions"."drug" = 'prasugrel') SELECT SUM(CASE WHEN "patients"."dod" IS NULL THEN 1 WHEN STRFTIME('%j', "patients"."dod") - STRFTIME('%j', "t1"."charttime") > 1460 THEN 1 ELSE 0 END) * 100 / NULLIF(COUNT(*), 0) FROM "t1" "t1" JOIN "t3" "t3" ON "t1"."charttime" < "t3"."startdate" AND "t1"."subject_id" = "t3"."subject_id" JOIN "patients" ON "patients"."subject_id" = "t1"."subject_id" WHERE STRFTIME('%j', CURRENT_TIME()) - STRFTIME('%j', "t1"."charttime") > 1460