Write a SQL query to analyze operational performance by joining guest satisfaction, staffing, and occupancy data to identify the biggest drivers of service issues.
Use the three daily operational tables provided. Calculate service issue rates and compare staffing gaps with occupancy levels for operational days having complete, valid measurements.
Output
- One row per property and operational driver.
- Columns:
property_id, driver_name, high_driver_issue_rate, low_driver_issue_rate, issue_rate_lift, and driver_rank.
- Include staffing-gap and occupancy-rate drivers, rank within each property by descending issue-rate lift, and break ties alphabetically by driver name.
Output details
- Calculate issue rate as
issue_count / survey_count * 100, staffing gap as scheduled_staff - actual_staff, and occupancy rate as rooms_occupied / rooms_available * 100.
- Exclude records where
survey_count <= 0, actual_staff is NULL, or rooms_available <= 0; also exclude NULL issue rates and driver values.
- Round high and low issue rates and issue-rate lift to
2 decimal places.
- Assign ascending driver values to four equal-count quartiles, using quartile
4 as high and quartile 1 as low.
- Use the difference between high and low average issue rates as the lift, and rank with dense ranking, placing
NULL lifts last.
- Order the final rows by
property_id ascending, then driver_rank ascending, then driver_name alphabetically.
