Как посчитать video startup time в SQL

Зачем startup time

Video startup time (VST) — время от click до first frame. Один из самых критичных QoE metrics: если юзер ждёт более 2 секунд — 20% abandon. Свыше 5 — 60% уходят. Targets: TV/desktop < 1s, mobile Wi-Fi < 2s, mobile cellular < 3s.

Формула

startup_time = first_frame_at - playback_requested_at

В миллисекундах для granularity.

Startup в SQL

SELECT
    DATE_TRUNC('day', requested_at)::DATE AS day,
    COUNT(*) AS views,
    AVG(EXTRACT(EPOCH FROM (first_frame_at - requested_at)) * 1000) AS avg_startup_ms,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (first_frame_at - requested_at)) * 1000) AS median_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (first_frame_at - requested_at)) * 1000) AS p95_ms
FROM video_playback_events
WHERE requested_at >= CURRENT_DATE - INTERVAL '7 days'
  AND first_frame_at IS NOT NULL
GROUP BY DATE_TRUNC('day', requested_at)
ORDER BY day;

Median 800ms — отлично. P95 5000ms — multiple users have bad experience.

По платформам

SELECT
    platform,
    AVG(startup_ms) AS avg_ms,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY startup_ms) AS p95_ms,
    COUNT(*) FILTER (WHERE startup_ms > 5000) AS slow_starts,
    COUNT(*) FILTER (WHERE startup_ms > 5000)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS slow_pct
FROM video_with_startup_ms
WHERE requested_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY platform
ORDER BY p95_ms DESC;

iOS обычно быстрее Android из-за hardware decode.

Startup vs abandonment

Корреляция startup time и abandonment:

SELECT
    CASE
        WHEN startup_ms < 1000 THEN '<1s'
        WHEN startup_ms < 2000 THEN '1-2s'
        WHEN startup_ms < 5000 THEN '2-5s'
        ELSE '5s+'
    END AS startup_bucket,
    COUNT(*) AS views,
    SUM(CASE WHEN abandoned_before_play THEN 1 ELSE 0 END)::NUMERIC * 100
    / NULLIF(COUNT(*), 0) AS abandon_pct
FROM video_playback_events
WHERE requested_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY MIN(startup_ms);

Каждая секунда задержки — 5-10% больше abandonment.

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

Ошибка 1. Server-side vs client-side timing. Server log shows «sent first byte». Client side — when frame rendered. Big difference.

Ошибка 2. Ad startup time mixed. Pre-roll ad load adds 1-3s. Track separately.

Ошибка 3. Cold start vs warm. First video session in app — cold (player init). Subsequent — warm. Разные.

Ошибка 4. Only successful starts. Failed starts (timeout, error) — НЕ included in mean. Track failure rate отдельно.

Ошибка 5. P50 vs P95. Average gives optimistic picture. P95 — real worst-case user experience.

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

• Как посчитать rebuffer rate в SQL
• Как посчитать video completion rate в SQL
• Как посчитать API latency в SQL
• Как посчитать app startup time в SQL

FAQ

Какой startup time норма?

TV / desktop: < 1s. Mobile Wi-Fi: < 2s. Mobile cellular: < 3s. Премиум — < 500ms.

Speed up через preload?

Yes — preconnect, preroll prefetch. -30-50% startup time.

Cold start fix?

Player warmup, asset preloading, lazy-load video metadata.

Abandonment threshold?

2s — sensitive viewers leave. 5s — most users.

CDN edge?

Yes — closer edge = faster. Cloudflare/Akamai edge servers worldwide.