> ## Documentation Index
> Fetch the complete documentation index at: https://docs.attio.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying with SQL

> Query your workspace data with read-only SQL

This guide shows how to explore your workspace schema and write queries against it. For the different ways to run a query, and their limitations, see the [SQL overview](/sql/overview). For information about what data is exposed to SQL, see [Available data](/sql/available-data).

## Query basics

Each object is exposed as a table named by its [slug](/docs/slugs-and-ids), with columns named after its attribute's slugs.
The `objects` schema is the default, so you can query an object directly by its slug (lists live under the `lists` schema, e.g. `FROM lists.sales`):

```sql theme={"system"}
SELECT name, value
FROM deals
WHERE value > 50000
ORDER BY value DESC;
```

## Joins

Record reference attributes (including relationship attributes) are exposed in SQL as columns containing structs with two text fields: `entity_definition_id`, the UUID of the target object, and `entity_instance_id`, the UUID of the target record.
These correspond to `target_object` and `target_record_id` in the [REST API](/rest-api/attribute-types/attribute-types-record-reference), except that `entity_definition_id` is always the object's UUID rather than its `api_slug`.

To follow a reference, join `entity_instance_id` against the target table's `record_id`.
As an example, every list entry exposes a `parent_record` reference column pointing at the record the entry belongs to, so you can join from a list back to its underlying object:

```sql theme={"system"}
SELECT lists.sales.entry_id, companies.name
FROM lists.sales
JOIN companies
  ON companies.record_id = lists.sales.parent_record.entity_instance_id;
```

### Multi-object references

A record reference attribute can be configured to allow more than one target object.
When it is, each value's `entity_definition_id` tells you which object that reference points at and can differ from row to row, so you can't resolve the whole column against one table.

Resolve it the same way as any reference, but filter each join on `entity_definition_id` so a join only considers the references that point at its object.
To pull in several object types at once, write one filtered join per object and `UNION ALL` them:

```sql theme={"system"}
SELECT referred_person.name.full_name AS person, companies.name AS referrer, 'company' AS referrer_type
FROM people referred_person
JOIN companies
  ON companies.record_id = referred_person.referral_source.entity_instance_id
 AND referred_person.referral_source.entity_definition_id = '<companies entity_definition_id>'
UNION ALL
SELECT referred_person.name.full_name AS person, referrer.name.full_name AS referrer, 'person' AS referrer_type
FROM people referred_person
JOIN people referrer
  ON referrer.record_id = referred_person.referral_source.entity_instance_id
 AND referred_person.referral_source.entity_definition_id = '<people entity_definition_id>';
```

For multi-select reference attributes, the technique is identical: just unnest the column in a CTE first (see below), then put the `entity_definition_id` filter on the unnested struct.

<Note>
  **Joining through record reference arrays**

  Multi-select record reference attributes (like `associated_deals` on people, or `associated_people` on deals) are stored as **arrays of structs**, where each struct is a reference of the shape `{entity_definition_id, entity_instance_id}`.
  To join from a record to the records it references, you need to unnest that array into one row per reference, then join on `entity_instance_id`.

  We don't support unnesting a table's own column directly in the `FROM` clause e.g. `FROM people, UNNEST(people.associated_deals) AS associated_deals`

  The supported method to write this join is to **unnest inside a CTE's `SELECT`, then field-access and join in the outer query.**

  ```sql theme={"system"}
  WITH person_deals AS (
    SELECT UNNEST(people.associated_deals) AS deal_ref, ...
    FROM people
  )
  SELECT ...
  FROM person_deals
  JOIN deals ON deals.record_id = person_deals.deal_ref.entity_instance_id;
  ```
</Note>

## References without a table

Not every reference resolves to a queryable table, so some can't be followed with a `JOIN`.

The most common case is **actor references**: built-in attributes like `created_by` that point at the workspace member, app, or automation responsible for something, rather than at another record.
These are exposed as a struct with two text fields, `type` (e.g. `workspace-member`, `api-token`, `system`) and `id`.

There's no `actors` table but you can filter and group on `created_by.type` and `created_by.id` directly.
For example, to see how your people records were created — teammates versus imports, enrichment, or automations:

```sql theme={"system"}
SELECT
  created_by.type                                    AS created_by,
  COUNT(*)                                           AS records,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM people
GROUP BY created_by.type
ORDER BY records DESC;
```

To turn `workspace-member` IDs into names, emails, or avatars, fetch the [list of workspace members](/rest-api/endpoint-reference/workspace-members/list-workspace-members) and cross-reference against it.

References to other entities that aren't queryable yet behave the same way.
Task references, for example the `next_due_task` attribute, use the entity-reference struct shape of `{entity_definition_id, entity_instance_id}` but have no table to join against.

## Example queries

Pipeline report weighted by deal stage:

```sql theme={"system"}
WITH stage_weights (title, probability) AS (
  VALUES
    ('Lead',       0.10),
    ('Evaluation', 0.40),
    ('Negotiation', 0.60),
    ('Signing',    0.80)
)
SELECT
  stage.title                                              AS stage,
  COUNT(record_id)                                         AS deal_count,
  ROUND(SUM(value)::numeric, 0)                            AS total_value,
  stage_weights.probability,
  ROUND((SUM(value) * stage_weights.probability)::numeric, 0) AS weighted_value
FROM deals
JOIN stage_weights ON stage_weights.title = stage.title
WHERE estimated_close_date BETWEEN '2026-04-01' AND '2026-06-30'
GROUP BY stage.title, stage_weights.probability
ORDER BY stage_weights.probability;
```

In-progress deals where the contacts we're talking to have the most Twitter followers:

```sql theme={"system"}
WITH person_deals AS (
  SELECT
    UNNEST(people.associated_deals) AS deal_ref,
    people.name.full_name           AS contact_name,
    people.twitter                  AS twitter_handle,
    people.twitter_follower_count   AS followers
  FROM people
  WHERE people.twitter_follower_count > 0
)
SELECT
  person_deals.contact_name,
  person_deals.twitter_handle,
  person_deals.followers,
  deals.record_id    AS deal_id,
  deals.name         AS deal_name,
  deals.stage.title  AS stage,
  deals.value        AS deal_value
FROM person_deals
JOIN deals ON deals.record_id = person_deals.deal_ref.entity_instance_id
WHERE deals.stage.title NOT IN ('Closed-Won', 'Closed-Lost', 'Unqualified', 'Duplicate')
ORDER BY person_deals.followers DESC
LIMIT 20;
```

Win rate by stage over the last two quarters:

```sql theme={"system"}
WITH closed_deals AS (
  SELECT
    record_id,
    stage.title AS final_stage,
    COALESCE(closed_won_date, closed_lost_date) AS closed_date
  FROM deals
  WHERE
    stage.title IN ('Closed-Won', 'Closed-Lost')
    AND COALESCE(closed_won_date, closed_lost_date) >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '6 months')
    AND COALESCE(closed_won_date, closed_lost_date) < DATE_TRUNC('quarter', CURRENT_DATE)
),
by_quarter AS (
  SELECT
    DATE_TRUNC('quarter', closed_date) AS quarter_start,
    COUNT(*) FILTER (WHERE final_stage = 'Closed-Won') AS won,
    COUNT(*) FILTER (WHERE final_stage = 'Closed-Lost') AS lost,
    COUNT(*) AS total
  FROM closed_deals
  GROUP BY DATE_TRUNC('quarter', closed_date)
  ORDER BY DATE_TRUNC('quarter', closed_date)
)
SELECT
  EXTRACT(YEAR FROM quarter_start)::TEXT || ' Q' || EXTRACT(QUARTER FROM quarter_start)::TEXT AS quarter,
  won,
  lost,
  total,
  ROUND(100.0 * won / NULLIF(total, 0), 1) AS win_rate_pct
FROM by_quarter
```