Как посчитать Time to Resolution в SQL

Зачем Time to Resolution

FRT (First Response Time) показывает первый отклик. TTR — full resolution. Юзер написал, через 5 min ответили — но проблема решена через 3 дня. TTR — про fully solved.

Формула

TTR = (ticket_resolved_at - ticket_created_at)

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

SELECT
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600) AS avg_ttr_hours,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600
    ) AS median_ttr_hours,
    PERCENTILE_CONT(0.9) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600
    ) AS p90_ttr_hours
FROM tickets
WHERE resolved_at IS NOT NULL
  AND created_at >= CURRENT_DATE - INTERVAL '90 days';

По сложности

SELECT
    complexity_level,
    COUNT(*) AS tickets,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600) AS avg_ttr_hours
FROM tickets
WHERE resolved_at IS NOT NULL
  AND created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY complexity_level
ORDER BY avg_ttr_hours;

Simple (password reset): minutes-hours. Complex (billing dispute): days-weeks.

SLA compliance

WITH ttr AS (
    SELECT
        ticket_id,
        priority,
        EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600 AS ttr_hours
    FROM tickets
    WHERE resolved_at IS NOT NULL
)
SELECT
    priority,
    COUNT(*) AS tickets,
    COUNT(*) FILTER (WHERE
        (priority = 'critical' AND ttr_hours <= 4)
        OR (priority = 'high' AND ttr_hours <= 24)
        OR (priority = 'normal' AND ttr_hours <= 72)
    )::NUMERIC * 100 / NULLIF(COUNT(*), 0) AS sla_compliance_pct
FROM ttr
GROUP BY priority;

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

Ошибка 1. Re-opens. Ticket resolved, юзер reopened, потом resolved again. TTR — sum или final?

Ошибка 2. Waiting on customer. Юзер не ответил 3 дня — ticket «waiting». Это включается в TTR или paused?

Ошибка 3. Business hours. TTR 48 hours during business vs 48 hours calendar — разные.

Ошибка 4. Resolved by user. Юзер сам решил проблему, ticket auto-closed. Это resolution agent?

Ошибка 5. Multi-component issues. Ticket «can't log in + payment failed». Two issues, one TTR.

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

• Как посчитать First Response Time в SQL
• Как посчитать CSAT в SQL
• Как посчитать CES в SQL
• SLA / SLO / SLI на собесе SA

FAQ

Какой TTR ok?

Зависит от priority. Critical: <4h. High: <24h. Normal: 1-3 days.

TTR vs FRT?

FRT — first response. TTR — full resolution. Customer cares about both.

Waiting on customer — pause TTR?

Yes, standard. «Active» time only.

High TTR — что делать?

1. Identify bottlenecks (which stage takes longest). 2) Better routing. 3) Knowledge base. 4) Specialized teams для complex issues.

TTR vs CSAT correlation?

Strong. Lower TTR → higher CSAT (быстрее resolve = happier).