DB Exercises
0
← Exercises/
Aggregations•3 of 12
PrevNext
AggregationsIntermediate

Hospital Ward Inpatient Length of Stay & Bed Occupancy

Scenario

Hospital bed administration needs to optimize ward resource allocation. Operations wants a breakdown of inpatient stays grouped by medical department to identify wards with high patient turnover vs extended average length of stay (ALOS).

Task
Aggregate patient stays in the `ward_admissions` table grouped by `department_name`. Calculate: 1. `department_name` 2. `total_admissions`: Count of all admissions 3. `avg_stay_days`: Average of `stay_duration_days` rounded to 1 decimal place 4. `max_stay_days`: Maximum `stay_duration_days` in that department Only include departments that have **at least 2** admissions. Order results by `avg_stay_days` descending.
Expected Output Format

Summary of department_name, total_admissions, avg_stay_days, and max_stay_days for wards with >= 2 admissions.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Hospital Ward Inpatient Length of Stay & Bed Occupancy” SQL Problem

Business Scenario

Hospital bed administration needs to optimize ward resource allocation. Operations wants a breakdown of inpatient stays grouped by medical department to identify wards with high patient turnover vs extended average length of stay (ALOS).

Expected Output & Criteria

Summary of department_name, total_admissions, avg_stay_days, and max_stay_days for wards with >= 2 admissions.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE ward_admissions (
        admission_id INT PRIMARY KEY,
        patient_name VARCHAR(100) NOT NULL,
        department_name VARCHAR(50) NOT NULL,
        stay_duration_days INT NOT NULL,
        discharge_disposition VARCHAR(30) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYCOUNTAVGROUND

Want more Aggregations practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Aggregations Track→