Contact Center Agent Performance Metrics

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:

1. agent_id, agent_name, call_date (date of call_start_ts)
2. calls_handled: count of calls where the agent was the primary handler
3. answered_calls: count of handled calls with answer_ts IS NOT NULL
4. avg_talk_seconds: average of (end_ts - answer_ts) in seconds for answered calls (exclude NULLs)
5. total_handle_seconds: sum of (end_ts - call_start_ts) in seconds for handled calls (includes wrap-up)
6. sla_answered_within_20s: percent of answered calls where (answer_ts - call_start_ts) <= 20 seconds (0–100, rounded to 2 decimals)
7. 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.