Data Analyst Interview Questions & Answers: Set 7, Hospital & Healthcare

SQL

1. 1.Find the average length of stay per department for patients admitted this year.

   Hide answer

   Answer: Length of stay (LOS) is discharge date minus admission date, averaged per department. I assume admissions(admission_id, patient_id, department_id, admit_date, discharge_date) and departments(department_id, department_name). I filter to admissions this year and only count discharged patients, because current inpatients have no discharge date yet and would distort the average. Subtracting two DATE values in PostgreSQL gives whole days; with timestamps you would use EXTRACT(EPOCH ...) / 86400.

   SELECT d.department_name,
          COUNT(*) AS admissions,
          ROUND(AVG(a.discharge_date - a.admit_date), 1) AS avg_los_days
   FROM admissions a
   JOIN departments d ON d.department_id = a.department_id
   WHERE a.admit_date >= DATE_TRUNC('year', CURRENT_DATE)
     AND a.discharge_date IS NOT NULL
   GROUP BY d.department_name
   ORDER BY avg_los_days DESC;

2. 2.Find patients readmitted within 30 days of discharge.

   Hide answer

   Answer: A readmission is a new admission for the same patient within 30 days after a previous discharge. The cleanest way is LEAD() to get each patient's next admission date, then compare it with the current discharge date. Table: admissions(admission_id, patient_id, admit_date, discharge_date). In real reporting you would usually exclude planned readmissions (e.g. scheduled chemotherapy) and transfers, so agree the definition with clinicians first.

   WITH ordered AS (
     SELECT patient_id, admission_id, admit_date, discharge_date,
            LEAD(admit_date) OVER (PARTITION BY patient_id
                                   ORDER BY admit_date) AS next_admit
     FROM admissions
   )
   SELECT patient_id, admission_id, discharge_date, next_admit,
          next_admit - discharge_date AS days_to_readmit
   FROM ordered
   WHERE next_admit IS NOT NULL
     AND next_admit - discharge_date BETWEEN 0 AND 30;