{"queries":[{"id":"content_volume","compiledQueryString":"SELECT\n strftime(captured_date, '%y-%m-%d') as captured_date,\n conversations_today as posts_today,\n comments_today,\n conversations_reactions + comments_reactions as reactions_today,\n chats_messages_today as messages_today\nFROM heycafe.platform_snapshots\nWHERE captured_date >= current_date - (\n SELECT CASE\n WHEN date_diff('day', MIN(captured_date), current_date) <= 14 THEN INTERVAL 7 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 45 THEN INTERVAL 30 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 90 THEN INTERVAL 60 DAY\n ELSE INTERVAL 90 DAY\n END FROM heycafe.platform_snapshots\n)\nORDER BY captured_date ASC","inputQueryString":"SELECT\n strftime(captured_date, '%y-%m-%d') as captured_date,\n conversations_today as posts_today,\n comments_today,\n conversations_reactions + comments_reactions as reactions_today,\n chats_messages_today as messages_today\nFROM heycafe.platform_snapshots\nWHERE captured_date >= current_date - (\n SELECT CASE\n WHEN date_diff('day', MIN(captured_date), current_date) <= 14 THEN INTERVAL 7 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 45 THEN INTERVAL 30 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 90 THEN INTERVAL 60 DAY\n ELSE INTERVAL 90 DAY\n END FROM heycafe.platform_snapshots\n)\nORDER BY captured_date ASC","compiled":false,"inline":true},{"id":"engagement_metrics","compiledQueryString":"SELECT\n ROUND(conversations_today * 1.0 / NULLIF(accounts_online_today, 0), 2) as posts_per_active_user,\n ROUND(comments_total * 1.0 / NULLIF(conversations_total, 0), 2) as comments_per_post,\n ROUND((conversations_reactions + comments_reactions) * 1.0 / NULLIF(conversations_total, 0), 2) as reactions_per_post,\n ROUND(conversations_tagged * 100.0 / NULLIF(conversations_total, 0), 1) as tagged_post_pct\nFROM heycafe.platform_snapshots\nORDER BY captured_date DESC\nLIMIT 1","inputQueryString":"SELECT\n ROUND(conversations_today * 1.0 / NULLIF(accounts_online_today, 0), 2) as posts_per_active_user,\n ROUND(comments_total * 1.0 / NULLIF(conversations_total, 0), 2) as comments_per_post,\n ROUND((conversations_reactions + comments_reactions) * 1.0 / NULLIF(conversations_total, 0), 2) as reactions_per_post,\n ROUND(conversations_tagged * 100.0 / NULLIF(conversations_total, 0), 1) as tagged_post_pct\nFROM heycafe.platform_snapshots\nORDER BY captured_date DESC\nLIMIT 1","compiled":false,"inline":true},{"id":"engagement_trends","compiledQueryString":"SELECT\n strftime(captured_date, '%y-%m-%d') as captured_date,\n ROUND(comments_today * 1.0 / NULLIF(conversations_today, 0), 2) as comment_to_post_ratio,\n ROUND(chats_messages_today * 100.0 / NULLIF(conversations_today + comments_today + chats_messages_today, 0), 1) as chat_share_pct,\n ROUND((conversations_reactions + comments_reactions) * 1.0 / NULLIF(conversations_today + comments_today, 0), 2) as reaction_intensity\nFROM heycafe.platform_snapshots\nWHERE captured_date >= current_date - (\n SELECT CASE\n WHEN date_diff('day', MIN(captured_date), current_date) <= 14 THEN INTERVAL 7 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 45 THEN INTERVAL 30 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 90 THEN INTERVAL 60 DAY\n ELSE INTERVAL 90 DAY\n END FROM heycafe.platform_snapshots\n)\nORDER BY captured_date ASC","inputQueryString":"SELECT\n strftime(captured_date, '%y-%m-%d') as captured_date,\n ROUND(comments_today * 1.0 / NULLIF(conversations_today, 0), 2) as comment_to_post_ratio,\n ROUND(chats_messages_today * 100.0 / NULLIF(conversations_today + comments_today + chats_messages_today, 0), 1) as chat_share_pct,\n ROUND((conversations_reactions + comments_reactions) * 1.0 / NULLIF(conversations_today + comments_today, 0), 2) as reaction_intensity\nFROM heycafe.platform_snapshots\nWHERE captured_date >= current_date - (\n SELECT CASE\n WHEN date_diff('day', MIN(captured_date), current_date) <= 14 THEN INTERVAL 7 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 45 THEN INTERVAL 30 DAY\n WHEN date_diff('day', MIN(captured_date), current_date) <= 90 THEN INTERVAL 60 DAY\n ELSE INTERVAL 90 DAY\n END FROM heycafe.platform_snapshots\n)\nORDER BY captured_date ASC","compiled":false,"inline":true},{"id":"trending_composition","compiledQueryString":"SELECT\n ROUND(SUM(CASE WHEN author_verified = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as verified_pct,\n ROUND(SUM(CASE WHEN has_attachments = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as media_pct,\n ROUND(SUM(CASE WHEN has_tag = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as tagged_pct,\n ROUND(SUM(CASE WHEN cafe_alias IS NOT NULL AND cafe_alias != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as from_cafe_pct,\n COUNT(*) as total_trending\nFROM heycafe.hot_conversations\nWHERE captured_date = (SELECT MAX(captured_date) FROM heycafe.hot_conversations)","inputQueryString":"SELECT\n ROUND(SUM(CASE WHEN author_verified = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as verified_pct,\n ROUND(SUM(CASE WHEN has_attachments = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as media_pct,\n ROUND(SUM(CASE WHEN has_tag = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as tagged_pct,\n ROUND(SUM(CASE WHEN cafe_alias IS NOT NULL AND cafe_alias != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) as from_cafe_pct,\n COUNT(*) as total_trending\nFROM heycafe.hot_conversations\nWHERE captured_date = (SELECT MAX(captured_date) FROM heycafe.hot_conversations)","compiled":false,"inline":true},{"id":"hot_conversations","compiledQueryString":"SELECT\n score,\n 'https://hey.cafe/@' || author_alias as author_url,\n author_alias,\n 'https://hey.cafe/' || cafe_alias as cafe_url,\n cafe_alias,\n SUBSTR(contents_preview, 1, 80) as preview,\n count_comments as comments,\n count_reactions as reactions,\n date_created\nFROM heycafe.hot_conversations\nWHERE captured_date = (SELECT MAX(captured_date) FROM heycafe.hot_conversations)\nORDER BY score DESC\nLIMIT 20","inputQueryString":"SELECT\n score,\n 'https://hey.cafe/@' || author_alias as author_url,\n author_alias,\n 'https://hey.cafe/' || cafe_alias as cafe_url,\n cafe_alias,\n SUBSTR(contents_preview, 1, 80) as preview,\n count_comments as comments,\n count_reactions as reactions,\n date_created\nFROM heycafe.hot_conversations\nWHERE captured_date = (SELECT MAX(captured_date) FROM heycafe.hot_conversations)\nORDER BY score DESC\nLIMIT 20","compiled":false,"inline":true}]}

Content & Engagement

Engagement Ratios

Content Volume

Engagement Depth

Chat Adoption

Trending Post Composition

Trending Conversations