dbdbexercises
Practice Free
← Syllabus|
Medium

15. ICU Patient Rolling 3-Reading Systolic Moving Average

MediumWindow FunctionsPostgreSQL 16PRO
Table: patient_vitals
+--------------+-----------+
| Column Name  | Type      |
+--------------+-----------+
| reading_id   | int       |
| patient_id   | int       |
| reading_time | timestamp |
| systolic_bp  | int       |
+--------------+-----------+

reading_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Intensive Care Unit (ICU) clinical telemetry uses moving averages across consecutive vital signs to filter out momentary measurement artifacts and detect true hemodynamic trends.

For each vital reading in `patient_vitals`, calculate a rolling 3-reading systolic blood pressure average using: `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` partitioned by `patient_id` and ordered by `reading_time` ascending. Return: 1. `reading_id` 2. `patient_id` 3. `reading_time` 4. `systolic_bp` 5. `rolling_avg_systolic`: `ROUND(AVG(systolic_bp) OVER (...), 1)` Order results by `patient_id` ascending, then by `reading_time` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Rolling systolic moving average per patient across consecutive readings.

Constraints:
  • Query must utilize clause(s): AVGOVERROWS BETWEEN
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “ICU Patient Rolling 3-Reading Systolic Moving Average” SQL Problem

Business Scenario

Intensive Care Unit (ICU) clinical telemetry uses moving averages across consecutive vital signs to filter out momentary measurement artifacts and detect true hemodynamic trends.

Expected Output & Criteria

Rolling systolic moving average per patient across consecutive readings.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE patient_vitals (
        reading_id INT PRIMARY KEY,
        patient_id INT NOT NULL,
        reading_time TIMESTAMP NOT NULL,
        systolic_bp INT NOT NULL
      );

Target SQL Clauses & Operators

AVGOVERROWS BETWEEN

Want more Window Functions practice?

Explore the full syllabus of 18 exercises and syntax guides.

View Window Functions Track