Your question is SQL: Top Sites by Unresolved Tickets. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query to join work orders, sites, and client tables to find the top 10 sites by unresolved tickets in the last 30 days.
Use the provided tables and return only tickets created within the last 30 days whose status is unresolved. Treat open, in_progress, and pending as unresolved statuses.
site_id, site_name, client_name, and unresolved_ticket_countsite_id ascending for ties| 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 |
| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique site identifier |
| site_name | VARCHAR(150) | Site display name |
| client_id | INT | Client responsible for the site |
| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Unique client identifier |
| client_name | VARCHAR(150) | Client display name |