Interview Question Bank · Date subtraction · AVG · IS NOT NULL
1. Average length of stay by department
EasyHealthcareQuestion #133
Concept: Date subtraction · AVG · IS NOT NULLshow
Subtracting two dates in Postgres gives a whole number of days, so discharge minus admission is a stay's length in nights. AVG skips NULLs on its own, but COUNT(*) does not: rows that haven't finished yet (no end date) still get counted. Filter them out with IS NOT NULL so the count and the average describe the same rows.
Example (different data):
SELECT warehouse, COUNT(*) AS delivered, ROUND(AVG(delivered_on - shipped_on), 2) AS avg_days FROM shipments WHERE delivered_on IS NOT NULL GROUP BY warehouse;
Your task
Larkspur Health's bed-management team wants to know how long inpatients typically stay in each department. Patients who are still in a bed have no discharge date yet.
Using only stays that have ended, return department, discharged_stays and avg_los_days (average of discharged_on minus admitted_on, rounded to 2 decimals). Sort by avg_los_days descending, then department.
Starting Postgres in your browser…
Checking your access…