Как посчитать time-to-hire в SQL

Зачем time-to-hire

Long time-to-hire (TTH) теряет candidates (другие компании быстрее), задерживает roadmap. Industry standard для tech engineer — 30-45 дней. Свыше 60 — рекрутинг тормозит. TTH декомпозируется на шаги: sourcing, screening, interview, offer-decision.

Формула

TTH = days from job_open to accepted_offer

Часто считают и time-to-fill = до start date. TTH более операционная метрика, TTF — деловая.

TTH в SQL

WITH hires AS (
    SELECT
        h.position_id,
        h.candidate_id,
        p.opened_at,
        h.offer_accepted_at,
        EXTRACT(EPOCH FROM (h.offer_accepted_at - p.opened_at)) / 86400 AS tth_days
    FROM hires h
    JOIN positions p USING (position_id)
    WHERE h.offer_accepted_at >= '2026-01-01'
)
SELECT
    COUNT(*) AS hires,
    AVG(tth_days) AS avg_tth,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tth_days) AS median_tth,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY tth_days) AS p95_tth,
    MIN(tth_days) AS fastest,
    MAX(tth_days) AS slowest
FROM hires;

Median 35, p95 90 — нормально. Median 60, p95 180 — медленно.

По роли и source

SELECT
    role_level,
    source,
    COUNT(*) AS hires,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tth_days) AS median_tth
FROM hires h
GROUP BY role_level, source
HAVING COUNT(*) >= 5
ORDER BY role_level, median_tth;

Referrals обычно 20-25 days. LinkedIn 40-60. Agency 30-90.

Decomposition по шагам

WITH stage_times AS (
    SELECT
        candidate_id,
        MAX(CASE WHEN stage = 'screened' THEN stage_at END) AS screened_at,
        MAX(CASE WHEN stage = 'phone_screen' THEN stage_at END) AS phone_at,
        MAX(CASE WHEN stage = 'onsite' THEN stage_at END) AS onsite_at,
        MAX(CASE WHEN stage = 'offer_extended' THEN stage_at END) AS offer_at,
        MAX(CASE WHEN stage = 'offer_accepted' THEN stage_at END) AS accepted_at
    FROM candidate_stage_events
    GROUP BY candidate_id
)
SELECT
    AVG(EXTRACT(EPOCH FROM (screened_at - applied_at)) / 86400) AS days_applied_to_screened,
    AVG(EXTRACT(EPOCH FROM (phone_at - screened_at)) / 86400) AS days_screened_to_phone,
    AVG(EXTRACT(EPOCH FROM (onsite_at - phone_at)) / 86400) AS days_phone_to_onsite,
    AVG(EXTRACT(EPOCH FROM (offer_at - onsite_at)) / 86400) AS days_onsite_to_offer,
    AVG(EXTRACT(EPOCH FROM (accepted_at - offer_at)) / 86400) AS days_offer_to_accepted
FROM stage_times
JOIN candidates USING (candidate_id)
WHERE accepted_at IS NOT NULL;

Каждый шаг — отдельная задержка. Top-bottleneck — focus area.

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

Ошибка 1. Median vs Mean. TTH skewed (хвост к 100+ дней). Median honestly.

Ошибка 2. Включать withdrawn candidates. Withdrawn не имеют accepted_at. INNER JOIN или фильтр.

Ошибка 3. Stop clock на pause. Hiring freeze 2 недели → TTH +14 дней искусственно. Track «active days» отдельно.

Ошибка 4. Не сегментировать по роли. Junior 20 days vs Senior 60. Усреднение не информативно.

Ошибка 5. Один TTH для всех level. SWE I vs Staff SWE — кардинально разные процессы.

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

• Как посчитать hiring funnel в SQL
• Как посчитать time-to-first-purchase в SQL
• Как посчитать time-to-convert в SQL
• Как посчитать employee turnover в SQL

FAQ

Какой TTH нормальный?

Tech engineer: 30-45 дней. Junior 20-30. Senior 45-60. Executive 90-180.

TTH vs TTF?

TTH = offer accepted. TTF = first day at office. TTF на 2-6 weeks больше.

Speed up через ATS?

Часто да. Greenhouse / Lever automate touchpoints, сокращают на 20-30%.

Долгие TTH плохо?

Yes — candidates declining больше, лучше offers от других. Но «speed at all costs» = плохие hires.

Per-step expectations?

Applied→screen 1-3 days. Phone→onsite 5-10 days. Onsite→offer 3-7 days.