CREATE TABLE vitalperiodic (vitalperiodicid NUMBER, patientunitstayid NUMBER, temperature NUMBER, sao2 NUMBER, heartrate NUMBER, respiration NUMBER, systemicsystolic NUMBER, systemicdiastolic NUMBER, systemicmean NUMBER, observationtime TIME)
CREATE TABLE lab (labid NUMBER, patientunitstayid NUMBER, labname CLOB, labresult NUMBER, labresulttime TIME)
CREATE TABLE medication (medicationid NUMBER, patientunitstayid NUMBER, drugname CLOB, dosage CLOB, routeadmin CLOB, drugstarttime TIME, drugstoptime TIME)
CREATE TABLE microlab (microlabid NUMBER, patientunitstayid NUMBER, culturesite CLOB, organism CLOB, culturetakentime TIME)
CREATE TABLE intakeoutput (intakeoutputid NUMBER, patientunitstayid NUMBER, cellpath CLOB, celllabel CLOB, cellvaluenumeric NUMBER, intakeoutputtime TIME)
CREATE TABLE patient (uniquepid CLOB, patienthealthsystemstayid NUMBER, patientunitstayid NUMBER, gender CLOB, age CLOB, ethnicity CLOB, hospitalid NUMBER, wardid NUMBER, admissionheight NUMBER, admissionweight NUMBER, dischargeweight NUMBER, hospitaladmittime TIME, hospitaladmitsource CLOB, unitadmittime TIME, unitdischargetime TIME, hospitaldischargetime TIME, hospitaldischargestatus CLOB)
CREATE TABLE diagnosis (diagnosisid NUMBER, patientunitstayid NUMBER, diagnosisname CLOB, diagnosistime TIME, icd9code CLOB)
CREATE TABLE cost (costid NUMBER, uniquepid CLOB, patienthealthsystemstayid NUMBER, eventtype CLOB, eventid NUMBER, chargetime TIME, cost NUMBER)
CREATE TABLE treatment (treatmentid NUMBER, patientunitstayid NUMBER, treatmentname CLOB, treatmenttime TIME)
CREATE TABLE allergy (allergyid NUMBER, patientunitstayid NUMBER, drugname CLOB, allergyname CLOB, allergytime TIME)

what was the top five diagnosis that had the highest five year mortality rate?
SELECT t4.diagnosisname FROM (SELECT t3.diagnosisname, DENSE_RANK() OVER (ORDER BY t3.c1 DESC) AS c2 FROM (SELECT t2.diagnosisname, 100 - SUM(CASE WHEN patient.hospitaldischargestatus = 'alive' THEN 1 WHEN STRFTIME('%j', patient.hospitaldischargetime) - STRFTIME('%j', t2.diagnosistime) > 5 * 365 THEN 1 ELSE 0 END) * 100 / COUNT(*) AS c1 FROM (SELECT t1.uniquepid, t1.diagnosisname, t1.diagnosistime FROM (SELECT patient.uniquepid, diagnosis.diagnosisname, diagnosis.diagnosistime FROM diagnosis JOIN patient ON diagnosis.patientunitstayid = patient.patientunitstayid GROUP BY patient.uniquepid, diagnosis.diagnosisname HAVING MIN(diagnosis.diagnosistime) = diagnosis.diagnosistime) AS t1 WHERE STRFTIME('%j', CURRENT_TIME()) - STRFTIME('%j', t1.diagnosistime) > 5 * 365) AS t2 JOIN patient ON t2.uniquepid = patient.uniquepid GROUP BY t2.diagnosisname) AS t3) AS t4 WHERE t4.c2 <= 5
WITH "t1" AS (SELECT "patient"."uniquepid", "diagnosis"."diagnosisname", "diagnosis"."diagnosistime" FROM "diagnosis" JOIN "patient" ON "diagnosis"."patientunitstayid" = "patient"."patientunitstayid" GROUP BY "patient"."uniquepid", "diagnosis"."diagnosisname" HAVING "diagnosis"."diagnosistime" = MIN("diagnosis"."diagnosistime")), "t3" AS (SELECT "t1"."diagnosisname", 100 - SUM(CASE WHEN "patient"."hospitaldischargestatus" = 'alive' THEN 1 WHEN STRFTIME('%j', "patient"."hospitaldischargetime") - STRFTIME('%j', "t1"."diagnosistime") > 1825 THEN 1 ELSE 0 END) * 100 / NULLIF(COUNT(*), 0) AS "c1" FROM "t1" "t1" JOIN "patient" ON "patient"."uniquepid" = "t1"."uniquepid" WHERE STRFTIME('%j', CURRENT_TIME()) - STRFTIME('%j', "t1"."diagnosistime") > 1825 GROUP BY "t1"."diagnosisname"), "t4" AS (SELECT "t3"."diagnosisname", DENSE_RANK() OVER (ORDER BY "t3"."c1" DESC) AS "c2" FROM "t3" "t3") SELECT "t4"."diagnosisname" FROM "t4" "t4" WHERE "t4"."c2" <= 5