Business Context
You’re working with the analytics team for a large B2C fintech (tens of millions of customers) that runs a 24/7 contact center. Leadership is rolling out a new agent coaching program and needs a reliable, queryable model to track agent performance and call duration across queues (e.g., billing, fraud, onboarding). Because the company is regulated, the model must support auditability (who handled what call, when, and what the outcome was) and consistent KPI definitions.
The data warehouse receives call events from the telephony system and agent roster updates from HR. Your job is to validate that the data model supports common reporting and then write a query that produces a daily performance rollup.
Task
Using the tables below, write a SQL query that returns daily agent performance for a given date range.
Requirements
Return one row per agent per day for calls that started in the date range ['2024-01-01', '2024-01-02'] (inclusive), with:
agent_id, agent_name, call_date (date of call_start_ts)calls_handled: count of calls where the agent was the primary handleranswered_calls: count of handled calls with answer_ts IS NOT NULLavg_talk_seconds: average of (end_ts - answer_ts) in seconds for answered calls (exclude NULLs)total_handle_seconds: sum of (end_ts - call_start_ts) in seconds for handled calls (includes wrap-up)sla_answered_within_20s: percent of answered calls where (answer_ts - call_start_ts) <= 20 seconds (0–100, rounded to 2 decimals)daily_rank_by_calls: rank agents within each day by calls_handled descending (ties share rank)
Additional notes:
- Ignore calls where
end_ts is NULL (still in progress).
- Use
agent_call_leg.is_primary = TRUE to attribute a call to a single agent.
- If an agent handled calls but none were answered,
avg_talk_seconds should be NULL and sla_answered_within_20s should be 0.
Table Definitions
agents
| column | type | description |
|---|
| agent_id | INT | Unique agent identifier |
| agent_name | VARCHAR(100) | Display name |
| hire_date | DATE | Date agent joined |
| site | VARCHAR(50) | Site/location (e.g., Phoenix) |
calls
| column | type | description |
|---|
| call_id | BIGINT | Unique call identifier |
| queue_name | VARCHAR(50) | Queue/routing group |
| call_start_ts | TIMESTAMP | When the call entered the system |
| answer_ts | TIMESTAMP | When the call was answered (NULL if abandoned) |
| end_ts | TIMESTAMP | When the call ended (NULL if ongoing) |
| disposition | VARCHAR(30) | Outcome (resolved, escalated, abandoned) |
agent_call_leg
| column | type | description |
|---|
| call_id | BIGINT | FK to calls |
| agent_id | INT | FK to agents |
| is_primary | BOOLEAN | True if this agent is the primary handler |
| leg_start_ts | TIMESTAMP | When agent leg started |
| leg_end_ts | TIMESTAMP | When agent leg ended |
Sample Data
agents
| agent_id | agent_name | hire_date | site |
|---|
| 101 | Ava Chen | 2023-06-12 | Phoenix |
| 102 | Mateo Ruiz | 2022-11-03 | Austin |
| 103 | Priya Nair | 2024-01-01 | Phoenix |
calls
| call_id | queue_name | call_start_ts | answer_ts | end_ts | disposition |
|---|
| 9001 | fraud | 2024-01-01 09:00:00 | 2024-01-01 09:00:10 | 2024-01-01 09:10:00 | resolved |
| 9002 | billing | 2024-01-01 09:05:00 | 2024-01-01 09:05:40 | 2024-01-01 09:20:00 | escalated |
| 9003 | fraud | 2024-01-01 10:00:00 | NULL | 2024-01-01 10:00:30 | abandoned |
| 9004 | onboarding | 2024-01-02 11:00:00 | 2024-01-02 11:00:05 | 2024-01-02 11:12:00 | resolved |
| 9005 | billing | 2024-01-02 12:00:00 | 2024-01-02 12:00:25 | 2024-01-02 12:10:00 | resolved |
agent_call_leg
| call_id | agent_id | is_primary | leg_start_ts | leg_end_ts |
|---|
| 9001 | 101 | TRUE | 2024-01-01 09:00:10 | 2024-01-01 09:10:00 |
| 9002 | 102 | TRUE | 2024-01-01 09:05:40 | 2024-01-01 09:20:00 |
| 9003 | 101 | TRUE | 2024-01-01 10:00:00 | 2024-01-01 10:00:30 |
| 9004 | 101 | TRUE | 2024-01-02 11:00:05 | 2024-01-02 11:12:00 |
| 9005 | 103 | TRUE | 2024-01-02 12:00:25 | 2024-01-02 12:10:00 |
Expected Output (for sample data)
| agent_id | agent_name | call_date | calls_handled | answered_calls | avg_talk_seconds | total_handle_seconds | sla_answered_within_20s | daily_rank_by_calls |
|---|
| 101 | Ava Chen | 2024-01-01 | 2 | 1 | 590.00 | 630 | 100.00 | 1 |
| 102 | Mateo Ruiz | 2024-01-01 | 1 | 1 | 860.00 | 900 | 0.00 | 2 |
| 101 | Ava Chen | 2024-01-02 | 1 | 1 | 715.00 | 720 | 100.00 | 1 |
| 103 | Priya Nair | 2024-01-02 | 1 | 1 | 575.00 | 600 | 0.00 | 1 |
