Analyzing Nursing Data with SQL

HardSQL · PostgreSQL

Problem

Tell me about a time you completed a CMS data case study and used SQL to analyze nursing data.

For this exercise, write a SQL query that analyzes active nursing facilities and their 2024 registered-nurse staffing records. Calculate each facility's valid reporting months and average RN hours per resident day. Treat zero resident days and missing RN hours as invalid measurements.

Output

One row per active facility, including facilities without valid records.
Columns: facility_name, state, months_reported, avg_rn_hours_per_resident_day, and staffing_band.
Classify facilities with at least two valid months and an average of at least 0.030 as Meets benchmark, other facilities with at least two valid months as Below benchmark, and all others as Insufficient data.
Order by state ascending, then facility name ascending.

Schema

nursing_facilities

Column	Type	Description
facility_idPK	INT	Unique nursing facility identifier
facility_name	VARCHAR(100)	Facility name
state	VARCHAR(2)	Two-letter facility state code
is_active	BOOLEAN	Whether the facility is currently active

nursing_staffing_monthly

Column	Type	Description
staffing_record_idPK	INT	Unique monthly staffing record identifier
facility_id	INT	Referenced nursing facility
record_month	DATE	First day of the reporting month
rn_hours	DECIMAL(10,2)	Registered-nurse hours reported for the month
resident_days	INT	Total resident days for the month

Tablesnursing_facilitiesnursing_staffing_monthly