SQL for Guest Complaints | Dataford Interview Questions

Write a SQL query to analyze guest complaints by department and day, including joins between complaint, guest, and department tables.

Use all complaints dated from 2025-01-01 through 2025-01-05. Include complaints with missing guest or department matches, labeling missing departments as Unassigned.

Output

One row per department and complaint day
Columns: department_name, complaint_day, complaint_count, guest_count, urgent_complaint_count, and avg_resolution_hours
Sort by department_name, then complaint_day ascending

Schema

complaints

Column	Type	Description
complaint_idPK	INT	Unique complaint identifier
guest_id	INT	Guest associated with the complaint
department_id	INT	Department responsible for the complaint
complaint_date	TIMESTAMP	Timestamp when the complaint was recorded
severity	VARCHAR(20)	Complaint severity level
resolution_hours	NUMERIC(8,2)	Hours required to resolve the complaint

guests

Column	Type	Description
guest_idPK	INT	Unique guest identifier
first_name	VARCHAR(80)	Guest first name
last_name	VARCHAR(80)	Guest last name
email	VARCHAR(255)	Guest email address

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Hotel operating department name

Your question is SQL for Guest Complaints. 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