Top Sites by Unresolved Tickets | Dataford Interview Questions

A facilities team wants to identify sites with the most unresolved work orders. Write a SQL query to return the top 10 sites by unresolved tickets created during the 30-day period ending on September 4, 2026.

Output

One row per site with columns site_id, site_name, client_name, and unresolved_ticket_count.
Include only work orders with status open, in_progress, or pending, created from August 5, 2026 through September 4, 2026, inclusive.
Exclude work orders without a matching site or client.
Sort by unresolved ticket count descending, then site_id ascending for ties, and return at most 10 rows.
Rows with statuses or dates outside the specified scope are excluded.

Schema

work_orders

Column	Type	Description
work_order_idPK	INT	Unique work order identifier
site_id	INT	Site associated with the work order
client_id	INT	Client associated with the work order
status	VARCHAR(30)	Current work order status
created_at	TIMESTAMP	Timestamp when the work order was created

sites

Column	Type	Description
site_idPK	INT	Unique site identifier
site_name	VARCHAR(150)	Site display name
client_id	INT	Client responsible for the site

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(150)	Client display name

Your question is Top Sites by Unresolved Tickets. 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