Lesson 7.6: Analytics Dashboard Queries

Real-Time Metrics

-- Dashboard: System health
SELECT
    (SELECT COUNT(*) FROM conversations WHERE started_at > NOW() - INTERVAL '1 hour') AS conversations_last_hour,
    (SELECT COUNT(*) FROM messages WHERE created_at > NOW() - INTERVAL '1 hour') AS messages_last_hour,
    (SELECT AVG(latency_ms) FROM model_predictions WHERE created_at > NOW() - INTERVAL '1 hour') AS avg_latency_ms,
    (SELECT pg_database_size(current_database())) AS db_size_bytes,
    (SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))
     FROM pg_statio_user_tables) AS cache_hit_ratio;

-- Dashboard: Top users by activity
SELECT
    user_id,
    COUNT(DISTINCT conversation_id) AS conversation_count,
    COUNT(*) AS message_count,
    MAX(created_at) AS last_active
FROM messages
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY user_id
ORDER BY message_count DESC
LIMIT 20;

-- Dashboard: Model performance trends
SELECT
    DATE_TRUNC('hour', created_at) AS hour,
    model_name,
    COUNT(*) AS predictions,
    AVG(latency_ms) AS avg_latency,
    AVG(confidence) AS avg_confidence
FROM model_predictions
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY hour, model_name
ORDER BY hour DESC, model_name;

Key Takeaways

• System health metrics track conversations, messages, latency, and cache hits
• Subqueries allow multiple metrics in single result set for dashboard
• pg_database_size monitors storage growth
• Cache hit ratio indicates query performance (aim for >95%)
• Top users query identifies power users and engagement patterns
• DATE_TRUNC aggregates time-series data by hour/day
• Time-windowed queries (1h, 7d, 24h) provide recent activity snapshots
• Performance trends track model latency and confidence over time