Как посчитать comment engagement в SQL

Зачем comment engagement

Comments — глубокий engagement (vs like = passive). Если post получает 100 likes, 5 comments — passive consumption. 100 likes, 50 comments — viral discussion. Comment engagement отделяет «I saw this» от «I care enough to type». Core metric для community-driven platforms.

Среднее на пост

SELECT
    COUNT(*) AS total_posts,
    COUNT(c.comment_id) AS total_comments,
    COUNT(c.comment_id)::NUMERIC / NULLIF(COUNT(*), 0) AS avg_comments_per_post
FROM posts p
LEFT JOIN comments c ON c.post_id = p.post_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days';

1-3 comments/post — норма. 10+ — high-engagement. < 0.5 — community problem.

Per-user commenters

WITH active_users AS (
    SELECT DISTINCT user_id FROM events
    WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
),
commenters AS (
    SELECT DISTINCT user_id FROM comments
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
    (SELECT COUNT(*) FROM active_users) AS active,
    (SELECT COUNT(*) FROM commenters) AS commented,
    (SELECT COUNT(*) FROM commenters)::NUMERIC * 100
    / NULLIF((SELECT COUNT(*) FROM active_users), 0) AS commenter_rate_pct;

9% commenters — стандарт (по 90-9-1 правилу).

Top engaging posts

SELECT
    p.post_id,
    p.title,
    COUNT(c.comment_id) AS comment_count,
    COUNT(DISTINCT c.user_id) AS unique_commenters,
    AVG(LENGTH(c.body)) AS avg_comment_length
FROM posts p
JOIN comments c USING (post_id)
WHERE p.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.post_id, p.title
ORDER BY comment_count DESC
LIMIT 20;

Top discussed — provocative / question-style posts. Analyze для content strategy.

Тренд

SELECT
    DATE_TRUNC('week', created_at)::DATE AS week,
    COUNT(*) AS comments,
    COUNT(DISTINCT user_id) AS unique_commenters,
    COUNT(*)::NUMERIC / NULLIF(COUNT(DISTINCT user_id), 0) AS comments_per_commenter
FROM comments
WHERE created_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', created_at)
ORDER BY week;

Растущий comments_per_commenter — power users становятся активнее. Если падает — discussion dying.

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

Ошибка 1. Treat comments = likes. Comments harder. Не сравнивайте 1:1.

Ошибка 2. Включать deleted comments. Filter status = 'published'. Otherwise inflated.

Ошибка 3. Bot detection ignored. Spam-bot comments inflate metrics. Manual or ML filter.

Ошибка 4. Average per post by all users. Top posts dominate. Median is better для central tendency.

Ошибка 5. Reply chains как separate comments. Replies на comment — это thread depth, не unique discussion. Track tree depth отдельно.

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

• Как посчитать engagement в SQL
• Как посчитать active creators в SQL
• Как посчитать UGC rate в SQL
• Как посчитать engagement rate в SQL

FAQ

Какой comment rate norm?

Engagement rate (likes + comments + shares) / views: 1-5% mainstream, 10%+ niche.

Comments vs likes — что важнее?

Comments — deeper. Likes — wider. Track both.

Comments per post распределение?

Power law: top 1% posts с 80% всех комментариев.

Bots impact?

Spam bots могут раздуть comments 30%. Spam filter обязателен.

Reply depth?

Threads with 5+ replies — real discussion. 1-2 — passive.