Analyzing Nursing Data with SQL

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

1. One row per active facility, including facilities without valid records.
2. Columns: facility_name, state, months_reported, avg_rn_hours_per_resident_day, and staffing_band.
3. 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.
4. Order by state ascending, then facility name ascending.