queries.sql

-- What is the percentage of mortality in every type of admission
-- 1. Mortality rate by admission type
SELECT admission_type,
       COUNT(*) AS total_admissions,
       SUM(CASE WHEN hospital_expire_flag = 1 THEN 1 ELSE 0 END) AS deaths,
       ROUND(100.0 * SUM(CASE WHEN hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS mortality_rate
FROM admissions
GROUP BY admission_type;

-- What are the Top 10 most prescribed drugs
-- 2. Top 10 most prescribed drugs
SELECT drug, COUNT(*) AS prescriptions
FROM prescriptions
GROUP BY drug
ORDER BY prescriptions DESC
LIMIT 100;
-- What is average stay time in hour for every admission type
-- 3. Average stay in hour
SELECT ad.admission_type, ROUND(AVG((julianday(ic.outtime) - julianday(ic.intime)) * 24 ), 2) AS avg_length_stay_inhour FROM icustays
AS ic JOIN admissions AS ad GROUP BY admission_type;

-- Which ICUs (MICU, SICU, CCU) had highest occupancy?
-- 4. ICU with decreasing occupancy
-- ICU Bed Usage: Which ICUs are most occupied?
SELECT first_careunit AS icu_type,
       COUNT(*) AS total_stays,
       ROUND(AVG(los), 2) AS avg_los_days
FROM icustays
GROUP BY icu_type
ORDER BY total_stays DESC;

--How often do patients get transferred between ICUs, and what does it imply?
-- 5. Transfers between ICUs
WITH transfer_pairs AS (
  SELECT hadm_id,
         prev_careunit AS from_unit,
         LEAD(curr_careunit) OVER (PARTITION BY hadm_id ORDER BY intime) AS to_unit
         FROM transfers
        )
SELECT from_unit, to_unit, COUNT(*) AS num_transfers
FROM transfer_pairs
WHERE to_unit IS NOT NULL
GROUP BY from_unit, to_unit
ORDER BY num_transfers DESC;

-- Which type of ICU cause more death in hospital
-- 6. Percentage of Death in hospital in during every type of ICU
SELECT
    ic.last_careunit,
    COUNT(*) AS total_cases,
    SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) AS deaths_in_hospital,
    ROUND(
        100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS death_rate_percent
FROM icustays AS ic
JOIN admissions AS ad
    ON ic.hadm_id = ad.hadm_id
GROUP BY ic.last_careunit
ORDER BY deaths_in_hospital DESC;

-- Which age group have more average time spend in hospitals
-- 7. Average ICU stay by age group
SELECT CASE
         WHEN age < 40 THEN '<40'
         WHEN age BETWEEN 40 AND 60 THEN '40-60'
         ELSE '>60'
       END AS age_group,
       ROUND(AVG(ic.los), 2) AS avg_los
FROM patient_summary AS pts
JOIN icustays AS ic ON pts.subject_id = ic.subject_id
GROUP BY age_group ORDER BY avg_los DESC;

-- What are the alive or death rate between different genders?
-- 8. Death and Alive rate
SELECT
    pt.gender,
    COUNT(*) AS total_patients,
    -- Count of survivors
    SUM(CASE WHEN ad.hospital_expire_flag = 0 THEN 1 ELSE 0 END) AS alive_count,
    -- Count of deaths
    SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) AS death_count,
    -- Percent alive
    ROUND(
        100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 0 THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS alive_percentage,
    -- Percent dead
    ROUND(
        100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS death_percentage
FROM patients AS pt
JOIN admissions AS ad
    ON pt.subject_id = ad.subject_id
GROUP BY pt.gender
ORDER BY death_percentage DESC;

-- What is the difference between death percentage of different genders of different age groups?
-- 9. Death percentage of different genders and different age groups
SELECT
    pt.gender,
    CASE
        WHEN age < 40 THEN '<40'
        WHEN age BETWEEN 40 AND 65 THEN '40-65'
        WHEN age BETWEEN 66 AND 89 THEN '66-89'
        ELSE '90+'
    END AS age_group,

    COUNT(*) AS total_patients,
    SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) AS death_count,
    ROUND(
        100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*),
        2
    ) AS death_percentage

FROM patient_summary AS pt
JOIN admissions AS ad
    ON pt.subject_id = ad.subject_id
GROUP BY pt.gender, age_group
ORDER BY age_group, pt.gender;

--What are the most common surgery ?
-- Top 20 most common surgery
SELECT d.short_title, COUNT(*) AS n_procedures
FROM procedures_icd AS p
JOIN d_icd_procedures AS d ON p.icd9_code = d.icd9_code
GROUP BY d.short_title
ORDER BY n_procedures DESC
LIMIT 20;

-- Which drug is given more often after surgery?
-- 11. Drug given n_times after surgery
SELECT d.short_title AS surgery,
       pr.drug_name_generic,
       COUNT(*) AS n_times
FROM procedures_icd AS p
JOIN d_icd_procedures AS d ON p.icd9_code = d.icd9_code
JOIN prescriptions AS pr ON p.hadm_id = pr.hadm_id
WHERE pr.drug_name_generic IS NOT NULL
GROUP BY surgery, pr.drug_name_generic
ORDER BY n_times DESC
LIMIT 100;

--What are the survival rate after centain surgeries ?
-- 12. Survival rate of different surgeries with different drugs
SELECT d.short_title AS surgery,
       pr.drug_name_generic,
       ROUND(100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS survival_rate
FROM procedures_icd AS p
JOIN d_icd_procedures AS d ON p.icd9_code = d.icd9_code
JOIN prescriptions AS pr ON p.hadm_id = pr.hadm_id
JOIN admissions AS ad ON pr.hadm_id = ad.hadm_id
GROUP BY surgery, pr.drug_name_generic
ORDER BY survival_rate DESC;

-- What are max,min, average of any drug and how many time it is given to patient ?
-- 13. Drugs summarization
SELECT
    pr.drug_name_generic,
    COUNT(*) AS n_prescriptions,
    ROUND(AVG(julianday(pr.enddate) - julianday(pr.startdate)), 2) AS avg_duration_days,
    ROUND(MIN(julianday(pr.enddate) - julianday(pr.startdate)), 2) AS min_duration,
    ROUND(MAX(julianday(pr.enddate) - julianday(pr.startdate)), 2) AS max_duration
FROM prescriptions AS pr
WHERE pr.startdate IS NOT NULL
  AND pr.enddate IS NOT NULL
  AND julianday(pr.enddate) > julianday(pr.startdate) -- avoid negatives
  AND (julianday(pr.enddate) - julianday(pr.startdate)) < 365 -- drop outliers
GROUP BY pr.drug_name_generic
HAVING COUNT(*) > 20 -- use the raw COUNT() here
ORDER BY avg_duration_days DESC, max_duration DESC
LIMIT 100;

-- Which are the short, medium and long term drug's effect?
-- 14. Buckets of drugs
WITH drug_durations AS (
    SELECT
        pr.drug_name_generic,
        julianday(pr.enddate) - julianday(pr.startdate) AS duration_days
    FROM prescriptions AS pr
    WHERE pr.startdate IS NOT NULL
      AND pr.enddate IS NOT NULL
      AND julianday(pr.enddate) > julianday(pr.startdate) -- avoid negatives
      AND (julianday(pr.enddate) - julianday(pr.startdate)) < 365 -- drop extreme outliers
)
SELECT
    drug_name_generic,
    COUNT(*) AS n_prescriptions,
    ROUND(AVG(duration_days), 2) AS avg_duration_days,
    SUM(CASE WHEN duration_days < 7 THEN 1 ELSE 0 END) AS short_term,
    SUM(CASE WHEN duration_days BETWEEN 7 AND 30 THEN 1 ELSE 0 END) AS medium_term,
    SUM(CASE WHEN duration_days > 30 THEN 1 ELSE 0 END) AS long_term
FROM drug_durations
GROUP BY drug_name_generic
HAVING COUNT(*) > 20
ORDER BY long_term DESC, avg_duration_days DESC
LIMIT 100;

-- What are the mortality rate of patients with specific drugs?
-- 15. Drugs effect
WITH drug_durations AS (
    SELECT
        pr.subject_id,
        pr.hadm_id,
        pr.drug_name_generic,
        julianday(pr.enddate) - julianday(pr.startdate) AS duration_days
    FROM prescriptions AS pr
    WHERE pr.startdate IS NOT NULL
      AND pr.enddate IS NOT NULL
      AND julianday(pr.enddate) > julianday(pr.startdate)   -- avoid negative
      AND (julianday(pr.enddate) - julianday(pr.startdate)) < 365 -- drop extreme outliers
),
classified AS (
    SELECT
        dd.subject_id,
        dd.hadm_id,
        dd.drug_name_generic,
        dd.duration_days,
        CASE
            WHEN dd.duration_days < 7 THEN 'short_term'
            WHEN dd.duration_days BETWEEN 7 AND 30 THEN 'medium_term'
            ELSE 'long_term'
        END AS duration_category
    FROM drug_durations AS dd
)
SELECT
    c.drug_name_generic,
    COUNT(*) AS n_prescriptions,
    ROUND(AVG(c.duration_days), 2) AS avg_duration_days,
    SUM(CASE WHEN c.duration_category = 'short_term' THEN 1 ELSE 0 END) AS short_term,
    SUM(CASE WHEN c.duration_category = 'medium_term' THEN 1 ELSE 0 END) AS medium_term,
    SUM(CASE WHEN c.duration_category = 'long_term' THEN 1 ELSE 0 END) AS long_term,
    ROUND(100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS mortality_rate_pct
FROM classified AS c
JOIN admissions AS ad ON c.hadm_id = ad.hadm_id
GROUP BY c.drug_name_generic
HAVING COUNT(*) > 20
ORDER BY mortality_rate_pct DESC, avg_duration_days DESC
LIMIT 100;

-- What is the mortality rate of patients with certain surgery and how long they stay in hospital
-- 16. Surgery summarization
SELECT
    p.long_title AS procedure,
    COUNT(*) AS n_patients,
    ROUND(AVG(julianday(ad.dischtime) - julianday(ad.admittime)), 2) AS avg_los_days,
    ROUND(100.0 * SUM(CASE WHEN ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS mortality_rate_pct
FROM procedures_icd AS pr
JOIN d_icd_procedures AS p ON pr.icd9_code = p.icd9_code
JOIN admissions AS ad ON pr.hadm_id = ad.hadm_id
GROUP BY p.long_title
HAVING COUNT(*) > 20
ORDER BY mortality_rate_pct DESC, avg_los_days DESC;

-- What is the mortality rate per diagnosis of per surgery
-- 17. Mortality rate per diagnosis per surgery
SELECT dd.long_title AS diagnosis,
       dp.long_title AS surgery,
       COUNT(*) AS n_cases,
       ROUND(100.0 * SUM(CASE WHEN a.hospital_expire_flag = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS mortality_rate
FROM diagnoses_icd AS di
JOIN d_icd_diagnoses AS dd ON di.icd9_code = dd.icd9_code
JOIN procedures_icd AS pr ON di.hadm_id = pr.hadm_id
JOIN d_icd_procedures AS dp ON pr.icd9_code = dp.icd9_code
JOIN admissions AS a ON di.hadm_id = a.hadm_id
GROUP BY dd.long_title, dp.long_title
ORDER BY mortality_rate DESC LIMIT 100;

-- What is the average LOS across different procedure?
-- 18. AVG ICU days
SELECT c.description AS procedure,
       ROUND(AVG(i.los), 2) AS avg_icu_days,
       COUNT(*) AS n_cases
FROM cptevents AS c
JOIN icustays AS i ON c.hadm_id = i.hadm_id
WHERE procedure IS NOT NULL
GROUP BY c.description
ORDER BY avg_icu_days DESC
LIMIT 20;

--What is the input and output fluid balance and it's  correlation with survival
-- 19. Correlation of death with input and output fluid balance
SELECT
    ic.subject_id,
    ic.hadm_id,
    ic.icustay_id,
    ROUND(SUM(inp.amount),1) AS total_input_ml,
    ROUND(SUM(out.value),1) AS total_output_ml,
    ROUND(SUM(inp.amount) - SUM(out.value),1) AS fluid_balance_ml,
    ad.hospital_expire_flag
FROM icustays ic
LEFT JOIN inputevents_mv inp ON ic.icustay_id = inp.icustay_id
LEFT JOIN outputevents out ON ic.icustay_id = out.icustay_id
JOIN admissions ad ON ic.hadm_id = ad.hadm_id
GROUP BY ic.icustay_id
ORDER BY fluid_balance_ml DESC
LIMIT 50;

--What is the antibiotics timing vs survivor
-- 20. Evaluate antibiotic timing vs survival
SELECT
    mb.hadm_id,
    mb.charttime AS culture_time,
    mb.org_name AS organism,
    pr.drug_name_generic,
    MIN(pr.startdate) AS antibiotic_start
FROM microbiologyevents mb
JOIN prescriptions pr
  ON mb.hadm_id = pr.hadm_id
WHERE
   culture_time IS NOT NULL
   AND organism IS NOT NULL
   AND
   pr.drug_name_generic LIKE '%Cillin%'
   OR pr.drug_name_generic LIKE '%Cef%'
   OR pr.drug_name_generic LIKE '%Vancomycin%'
GROUP BY mb.hadm_id, organism
ORDER BY culture_time;

--What is the lab average labvaluenumber befor death
-- 21. Lab deterioration before death
SELECT
    ad.hadm_id,
    lb.itemid,
    dl.label AS test_name,
    ROUND(AVG(lb.valuenum), 2) AS avg_value
FROM admissions ad
JOIN labevents lb ON ad.hadm_id = lb.hadm_id
JOIN d_labitems dl ON lb.itemid = dl.itemid
WHERE
    lb.valuenum IS NOT NULL AND
  ad.hospital_expire_flag = 1
  AND lb.charttime BETWEEN ad.admittime AND ad.dischtime
GROUP BY lb.itemid
ORDER BY avg_value;

--What is the situation of vital organ between dead and alive patient?
-- 22. Vital trend in ICU
SELECT
    ce.icustay_id,
    di.label AS vital_name,
    ROUND(AVG(ce.valuenum),2) AS avg_vital,
    ad.hospital_expire_flag
FROM chartevents ce
JOIN d_items di ON ce.itemid = di.itemid
JOIN admissions ad ON ce.hadm_id = ad.hadm_id
WHERE di.label IN ('Heart Rate','Mean BP','SpO2')
GROUP BY ce.icustay_id, di.label;

SELECT
    valueuom,
    ROUND(AVG(valuenum), 2) AS avg_value,
    SUM(CASE WHEN warning = 1 THEN 1 ELSE 0 END) AS positive_warning,
    SUM(CASE WHEN warning = 0 THEN 1 ELSE 0 END) AS negative_warning
FROM chartevents
WHERE valuenum IS NOT NULL
GROUP BY valueuom
ORDER BY positive_warning DESC;

-- Did the person which were given dose and have warning die?
-- 23. Compare warning rate with mortality
SELECT
    di.icd9_code,
    di.short_title,
    ce.valueuom,
    ROUND(AVG(ce.valuenum), 2) AS avg_value,
    ROUND(100.0 * SUM(CASE WHEN ce.warning = 1 AND ad.hospital_expire_flag = 1 THEN 1 ELSE 0 END)
           / NULLIF(SUM(CASE WHEN ce.warning = 1 THEN 1 ELSE 0 END), 0), 2) AS mortality_rate_with_warning
FROM chartevents AS ce
JOIN admissions AS ad ON ce.hadm_id = ad.hadm_id
JOIN diagnoses_icd AS d ON ad.hadm_id = d.hadm_id
JOIN d_icd_diagnoses AS di ON d.icd9_code = di.icd9_code
WHERE ce.valuenum IS NOT NULL
GROUP BY d.icd9_code, ce.valueuom
ORDER BY mortality_rate_with_warning DESC
LIMIT 100;

--What are the average prescribed across different administration at multiple descriptive levels
--24. Compare prescribed vs. actual administration at multiple descriptive levels
SELECT TRIM(ordercategoryname) as category,
        TRIM(secondaryordercategoryname) as second_cat,
        TRIM(ordercomponenttypedescription) as component,
        TRIM(ordercategorydescription) as description,
       ROUND(AVG(originalamount), 2) AS avg_original_amount,
       ROUND(AVG(totalamount), 2) AS avg_final_amount,
       ROUND(AVG(originalrate), 2) AS avg_original_rate,
       ROUND(AVG(rate), 2) AS avg_final_rate
FROM inputevents_mv
WHERE (originalamount IS NOT NULL AND totalamount IS NOT NULL)
   OR (originalrate IS NOT NULL AND rate IS NOT NULL)
GROUP BY category,
        second_cat,
        component,
        description
LIMIT 100;
--What is the most common infection case that is detected ?
-- 24. Infection pattern with antibiotics
SELECT spec_type_desc AS specimen_type,
       org_name AS organism,
       COUNT(*) AS cases_detected
FROM microbiologyevents
WHERE org_name IS NOT NULL
GROUP BY spec_type_desc, org_name
ORDER BY cases_detected DESC
LIMIT 100;

--Which diagnose is more correlated to readmission?
-- 25. Diagnose with more readmission
WITH readmissions AS (
    SELECT subject_id, COUNT(hadm_id) AS n_admissions
    FROM admissions
    GROUP BY subject_id
    HAVING n_admissions > 1
)
SELECT dd.long_title AS diagnosis,
       COUNT(DISTINCT r.subject_id) AS n_patients,
       100 * COUNT(DISTINCT r.subject_id)/ (SELECT COUNT(DISTINCT subject_id) FROM readmissions) AS pct_patients
FROM readmissions r
JOIN diagnoses_icd d ON r.subject_id = d.subject_id
JOIN d_icd_diagnoses dd ON d.icd9_code = dd.icd9_code
GROUP BY dd.long_title
ORDER BY pct_patients DESC
LIMIT 20;

--Which lab abnormality cause more death afterward?
-- 26. Lab abnormalities VS Mortality
SELECT l.itemid, d.label,
       COUNT(*) AS n_tests,
       ROUND(100.0 * SUM(CASE WHEN l.flag = 'abnormal' THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_abnormal,
       ROUND(100.0 * SUM(CASE WHEN a.hospital_expire_flag = 1 AND l.flag = 'abnormal' THEN 1 ELSE 0 END) / COUNT(*), 2) AS mortality_with_abnormal
FROM labevents l
JOIN admissions a ON l.hadm_id = a.hadm_id
JOIN d_labitems d ON l.itemid = d.itemid
GROUP BY l.itemid, d.label
HAVING n_tests > 500
ORDER BY mortality_with_abnormal DESC
LIMIT 100;