Drill 1 of 12 in Healthcare questions
0

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…