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

Зачем watch time

Watch time — total seconds, проведённые в просмотре. YouTube алгоритм оптимизируется на это, не на views. Watch time показывает не «кликнули», а «реально смотрели». Главная metric для recommender систем video-платформ.

Формула

total_watch_time = SUM(seconds_watched)
avg_watch_time_per_view = SUM(seconds_watched) / COUNT(views)
avg_watch_time_per_user = SUM(seconds_watched) / COUNT(DISTINCT user_id)

Watch time в SQL

SELECT
    DATE_TRUNC('day', view_started_at)::DATE AS day,
    SUM(seconds_watched) / 3600.0 AS total_hours,
    AVG(seconds_watched) AS avg_seconds_per_view,
    SUM(seconds_watched) / NULLIF(COUNT(DISTINCT user_id), 0) AS avg_seconds_per_user
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', view_started_at)
ORDER BY day;

Total hours per day — main growth metric для video platform.

Per-user watch time

SELECT
    user_id,
    COUNT(*) AS views,
    SUM(seconds_watched) / 60.0 AS total_minutes_watched,
    SUM(seconds_watched)::NUMERIC / NULLIF(COUNT(*), 0) AS avg_seconds_per_view
FROM video_view_events
WHERE view_started_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING SUM(seconds_watched) >= 60
ORDER BY total_minutes_watched DESC
LIMIT 100;

Top users — heavy viewers. Sentinel для retention.

По дням и категориям

SELECT
    DATE_TRUNC('day', view_started_at)::DATE AS day,
    v.category,
    SUM(e.seconds_watched) / 3600.0 AS total_hours,
    COUNT(DISTINCT e.user_id) AS unique_viewers
FROM video_view_events e
JOIN videos v USING (video_id)
WHERE view_started_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE_TRUNC('day', view_started_at), v.category
ORDER BY day, total_hours DESC;

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

Ошибка 1. View time vs watch time. View time — время на странице. Watch time — фактический playback. Не путать.

Ошибка 2. Не учитывать pause. Юзер поставил на pause 30 минут → 30 минут «watched»? Нет, нужно отличать active playback.

Ошибка 3. Включать autoplay в metric. Autoplay watch time inflated. Discount autoplay or track separately.

Ошибка 4. Sum > duration. Если seconds_watched суммируется ошибочно (multiple sessions), может быть > video duration. Cap to min(seconds, duration).

Ошибка 5. Игнорировать speed playback. Юзер смотрит на 2× → playback time 50% от content. Adjust в business metric.

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

• Как посчитать video completion rate в SQL
• Как посчитать engagement в SQL
• Как посчитать session depth в SQL
• Как посчитать DAU в SQL

FAQ

Watch time per user — какой?

Casual viewers 5-15 мин/день. Power users 60+ мин/день.

Total hours — что значит?

Aggregate engagement на platform. YouTube measure billions of hours yearly.

Watch time vs views?

Views — clicks. Watch time — actual time. Recommender optimize на watch time.

Pause считается?

Зависит от tracker. Если playback paused, watch time не должен увеличиваться.

Speed 2× учитывать?

Yes — adjust в content-time units. Иначе heavy 2× watchers распознаются как low engagement.