Querying with SQL - Attio Docs

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. For information about what data is exposed to SQL, see Available data.

Query basics

Each object is exposed as a table named by its slug, 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):

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, 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:

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:

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.

Joining through record reference arraysMulti-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_dealsThe supported method to write this join is to unnest inside a CTE’s SELECT, then field-access and join in the outer query.

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;

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:

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 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:

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:

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:

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

​Query basics

​Joins

​Multi-object references

​References without a table

​Example queries

Query basics

Joins

Multi-object references

References without a table

Example queries