SQL for Complaint Drivers | Dataford Interview Questions

Write a SQL query to analyze weekly guest complaints by location and category, and identify the top complaint drivers over time at Hard Rock International.

Use the provided complaint, location, and category data. Include observed complaint categories, calculate their weekly counts and changes from the previous week, and identify the top two drivers within each location and week.

Output

One row per observed week, location, and complaint category
Columns: week_start, location_name, category_name, complaint_count, prior_week_count, week_over_week_change, location_category_rank, and is_top_driver
Order by week_start, location_name, location_category_rank, and category_name

Schema

locations

Column	Type	Description
location_idPK	INT	Unique Hard Rock International location identifier
location_name	VARCHAR(120)	Location name
country	VARCHAR(80)	Country where the location operates

complaint_categories

Column	Type	Description
category_idPK	INT	Unique complaint category identifier
category_name	VARCHAR(100)	Complaint classification

guest_complaints

Column	Type	Description
complaint_idPK	INT	Unique guest complaint identifier
location_id	INT	Referenced Hard Rock location
category_id	INT	Referenced complaint category
received_at	TIMESTAMP	Timestamp when the complaint was received

Your question is SQL for Complaint Drivers. 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