Common Query Patterns

SQL examples for common analytics queries across organization tables.

Query Examples. These examples cover common patterns for analyzing your organization's data.

Basic Queries

-- Inspect any table structure
SELECT *
FROM conversations
LIMIT 1;

-- Count records by type
SELECT
  COUNT(*) as total,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(DISTINCT service_id) as unique_services
FROM conversations
WHERE org_id = 'your-org-id';

Join Queries

-- Get all messages for a user's conversations
SELECT
  m.*,
  c.service_id,
  c.created_at as conversation_started
FROM messages m
JOIN conversations c ON m.conversation_id = c._id
WHERE c.user_id = 'user-123'
ORDER BY m.created_at DESC;

-- User activity with service details
SELECT
  u.email,
  s.name as service_name,
  COUNT(c._id) as conversation_count,
  MAX(c.created_at) as last_activity
FROM users u
LEFT JOIN conversations c ON u.user_id = c.user_id
LEFT JOIN services s ON c.service_id = s._id
GROUP BY u.email, s.name;

Analytics Queries

-- Daily conversation volume
SELECT
  DATE(created_at) as day,
  COUNT(*) as conversations,
  COUNT(DISTINCT user_id) as unique_users
FROM conversations
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY day DESC;

-- Service performance metrics
SELECT
  s.name,
  COUNT(c._id) as total_conversations,
  AVG(CASE WHEN c.is_finished THEN 1 ELSE 0 END) as completion_rate,
  COUNT(DISTINCT c.user_id) as unique_users
FROM services s
LEFT JOIN conversations c ON s._id = c.service_id
WHERE s.is_active = true
GROUP BY s.name;