DB Exercises
0
← Exercises/
CTEs•10 of 12
PrevNext
CTEsAdvancedPRO

Surgical Suite Block Time Utilization Rate

Scenario

Hospital surgical leadership monitors surgical suite utilization. Surgical suites are scheduled in 480-minute blocks (8 hours). Efficiency audits compute what percentage of allocated block time was utilized for active surgery.

Task
Join `operating_rooms` with `surgeries` using a CTE to aggregate utilized minutes. Calculate: 1. `room_name` 2. `allocated_minutes`: 480 (standard shift) 3. `utilized_minutes`: Sum of `duration_minutes` for surgeries conducted in that room 4. `utilization_pct`: `ROUND(100.0 * utilized_minutes / 480, 1)` Order results by `utilization_pct` descending.
Expected Output Format

Surgical rooms with total surgery minutes and utilization percentage.

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 “Surgical Suite Block Time Utilization Rate” SQL Problem

Business Scenario

Hospital surgical leadership monitors surgical suite utilization. Surgical suites are scheduled in 480-minute blocks (8 hours). Efficiency audits compute what percentage of allocated block time was utilized for active surgery.

Expected Output & Criteria

Surgical rooms with total surgery minutes and utilization percentage.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

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

CREATE TABLE operating_rooms (
        room_id INT PRIMARY KEY,
        room_name VARCHAR(50) NOT NULL
      );

      CREATE TABLE surgeries (
        surgery_id INT PRIMARY KEY,
        room_id INT REFERENCES operating_rooms(room_id),
        duration_minutes INT NOT NULL
      );

Target SQL Clauses & Operators

WITHGROUP BYROUND

Want more CTEs practice?

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

View CTEs Track→