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
التالي · الدرس 45 من 45
Deployment and Maintenance
قراءة 1 دقيقة
أنهيت 44 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

