# Common Query Patterns

{% hint style="info" %}
**Query Examples**\
These examples demonstrate common patterns for analyzing your organization's data.
{% endhint %}

{% tabs %}
{% tab title="Basic Queries" %}

```sql
-- 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';
```

{% endtab %}

{% tab title="Join Queries" %}

```sql
-- 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;
```

{% endtab %}

{% tab title="Analytics Queries" %}

```sql
-- 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;
```

{% endtab %}
{% endtabs %}