Provider Analytics Path · Mission 17 of 25Hard

ED wait times by day of week

Use EXTRACT(DOW), AVG with filtering, and a window function for baseline comparison

The Brief

James HartwellVP of Operationsslack-dm

avg ED wait time by day of week. exclude the bad data over 480 min. also show an overall average across all days as a baseline column so i can see which days are above/below average. weekends are killing us — need proof.

You'll practice

EXTRACT(DOW)AVG() OVER()

Tables & columns available

fact_quality_metricsfact7 columns

Column	Type	Key
metric_id	INT	PK
encounter_id	INT	FK → fact_encounters
patient_id	INT	FK → dim_patients
metric_type	TEXT
metric_value	REAL
measurement_date	TEXT
reported_by	TEXT

fact_encountersfact10 columns

Column	Type	Key
encounter_id	INT	PK
patient_id	INT	FK → dim_patients
provider_id	INT	FK → dim_providers
department_id	INT	FK → dim_departments
admit_date	TEXT
discharge_date	TEXT
diagnosis_code	TEXT
diagnosis_description	TEXT
encounter_type	TEXT
discharge_disposition	TEXT

dim_departmentsdim6 columns

Column	Type	Key
department_id	INT	PK
department_name	TEXT
department_type	TEXT
floor	INT
bed_count	INT
manager_name	TEXT

Hints (3)

Each hint you reveal reduces the XP you can earn. Try the query first.

Hint 1

Two data quality issues compound: the three ED name variants AND the impossible wait times over 480. Filter both before you aggregate

Hint 2

Group by day-of-week with `EXTRACT`, and add a window function with no partition to put the grand average on every row

Hint 3

`SELECT EXTRACT(___ FROM measurement_date::date) AS day_of_week, AVG(metric_value) AS avg_wait, AVG(AVG(metric_value)) OVER () AS overall_avg ... WHERE metric_value <= ___ AND department_name IN (___, ___, ___) GROUP BY ___`

← Previous

Mission 16

Mission 18