Как посчитать Average Handle Time в SQL

Зачем AHT

Average Handle Time — talk + hold + wrap-up. Operational metric. Низкий AHT = больше calls per agent, но качество страдает если слишком низкий.

Формула

AHT = (Total Talk Time + Total Hold Time + Total Wrap-up Time) / Calls

Базовый расчёт

Данные: calls(call_id, agent_id, talk_seconds, hold_seconds, wrap_seconds, call_ended_at).

SELECT
    DATE_TRUNC('day', call_ended_at) AS day,
    COUNT(*) AS total_calls,
    AVG(talk_seconds + hold_seconds + wrap_seconds) AS aht_seconds,
    AVG(talk_seconds + hold_seconds + wrap_seconds) / 60.0 AS aht_minutes,
    AVG(talk_seconds) AS avg_talk,
    AVG(hold_seconds) AS avg_hold,
    AVG(wrap_seconds) AS avg_wrap
FROM calls
WHERE call_ended_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

По агентам

SELECT
    agent_id,
    COUNT(*) AS calls,
    AVG(talk_seconds + hold_seconds + wrap_seconds) AS aht,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY talk_seconds + hold_seconds + wrap_seconds) AS median_aht
FROM calls
WHERE call_ended_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY agent_id
HAVING COUNT(*) >= 50
ORDER BY aht;

Top performers — short AHT + high CSAT. Outliers — high AHT alone may мean struggling agent or complex calls.

По типам

SELECT
    call_reason,
    COUNT(*) AS calls,
    AVG(talk_seconds + hold_seconds + wrap_seconds) AS aht,
    AVG(csat_score) AS avg_csat
FROM calls
WHERE call_ended_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY call_reason
ORDER BY calls DESC;

Different call types have different natural AHT. Compare within type.

Частые ошибки

Ошибка 1. Только talk time. Hold + wrap matter. Real cost includes all 3.

Ошибка 2. Outliers. Один call 4 hours skews average. Use median + p90.

Ошибка 3. Optimize AHT в isolation. AHT down — but FCR down тоже? Bad trade.

Ошибка 4. Wrap не tracked. Если agents don't log wrap, AHT undercounted.

Ошибка 5. Idle time confusion. Idle (between calls) — не AHT. Different metric (utilization).

Связанные темы

• Incident response на собесе SA
• Как посчитать first time fix rate в SQL
• Как посчитать CSAT в SQL
• Как посчитать NPS в SQL

FAQ

Какой AHT ok?

Industry varies. Tech support: 8-12 min. Banking: 5-7 min. E-com: 3-5 min.

AHT down — это хорошо?

Только если quality (CSAT, FCR) holds. Rushed calls = bad UX.

Wrap-up time?

Post-call admin: ticket update, follow-up email. Should be ≤ talk time.

AHT по channel?

Voice ≈ 5-10 min. Chat ≈ 3-5 min (multi-task). Email — separate (response time).

Median vs Average?

Long tail. Use median + p90. Average — biased by outliers.