Common Query Patterns
-- 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';-- 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;-- 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;Last updated
Was this helpful?