Agent Resolution Rate

Problem

A help desk tracks its support agents and the outcome of each ticket they handle.

Table: agents

| Column Name | Type |
|-------------|----------|
| agent_id | int |
| hired_at | datetime |

agent_id is the unique key for this table. Each row records when an agent was hired.

Table: tickets

| Column Name | Type |
|-------------|----------|
| agent_id | int |
| opened_at | datetime |
| outcome | text |

(agent_id, opened_at) is the primary key for this table. agent_id references the agents table. outcome is one of 'resolved' or 'escalated'. Each row is one ticket the agent handled and how it ended.

An agent's resolution rate is the number of 'resolved' tickets divided by the total number of tickets they handled. An agent who handled no tickets has a resolution rate of 0. Round the rate to two decimal places.

Return agent_id and resolution_rate for every agent, in any order.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 4 rows with the columns agent_id, resolution_rate.

Starter code (SQL)

SELECT *
FROM agents;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agents With a Dominant Contact ChannelMedium