Occupancy High, GSS Down | Dataford Interview Questions

Write a SQL query to find days when occupancy was high but Guest Satisfaction Scores fell below the property average.

Treat occupancy of 90% or higher as high. Calculate each property's average from its non-null daily Guest Satisfaction Scores, and calculate one daily score per property and date.

Output

One row per qualifying property and date
Columns: property_id, property_name, occupancy_date, occupancy_rate, daily_gss, and property_avg_gss
Include only high-occupancy days with a daily score below the property average
Order by property_id, then occupancy_date ascending

Schema

properties

Column	Type	Description
property_idPK	INT	Unique property identifier
property_name	VARCHAR(150)	Property display name

daily_occupancy

Column	Type	Description
occupancy_idPK	INT	Unique occupancy record identifier
property_id	INT	Referenced property
occupancy_date	DATE	Date of the occupancy measurement
occupancy_rate	DECIMAL(5,4)	Occupied rooms divided by available rooms

guest_satisfaction

Column	Type	Description
feedback_idPK	INT	Unique feedback record identifier
property_id	INT	Referenced property
feedback_date	DATE	Date associated with the feedback
gss_score	DECIMAL(4,2)	Guest Satisfaction Score from 0 to 10

Your question is Occupancy High, GSS Down. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem