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

# Query with SQL

> Compose read-only SQL over Analytics ownership keys, detail grains, and explicit relationships.

Your Analytics credential can run arbitrary read-only `SELECT` queries over `reporting.*`. SQL uses
the same relations as Power BI, but each query chooses its own grain, joins, filters, and result.

Read [Relationships and data model](/analytics/relationships) before combining repeating detail.

## Use this process

For each question:

1. Define what one result row should represent.
2. Choose the relation with that grain.
3. Join ownership through `entity_id` or `component_id`.
4. Traverse explicit relationship views when the question crosses independent objects.
5. Use `EXISTS` for detail that only filters the result.
6. Pre-aggregate detail before combining measures from different grains.
7. Validate codes, roles, currency, units, dates, nulls, and known records.

Do not begin with `shipments` or `one_off_quotes` by habit. Begin with the relation that matches the
answer.

## Choose the starting grain

| Question measures                      | Start from                       | One row means                              |
| -------------------------------------- | -------------------------------- | ------------------------------------------ |
| Current recognized objects             | `business_entities`              | One current full entity                    |
| Known identities, including references | `entity_identities`              | One full or reference-only identity        |
| Charge activity                        | `entity_charges`                 | One charge line                            |
| Container penalties                    | `entity_container_penalties`     | One penalty term                           |
| Packing-line cargo                     | `entity_packing_lines`           | One packing line                           |
| Explicit business links                | `entity_relationships`           | One observed edge                          |
| Component references                   | `entity_component_relationships` | One observed component reference           |
| Source values                          | `cargowise_xml_observations`     | One parsed scalar or attribute observation |

Use a wide convenience view only when the report intentionally uses one subtype and its columns.

## Example 1: Inventory current entities

This query starts from `business_entities` because one result row summarizes one entity type:

```sql theme={null}
SELECT
  entities.entity_type,
  count(*) AS entity_count,
  count(*) FILTER (
    WHERE entities.total_weight_kg IS NOT NULL
  ) AS entities_with_normalized_weight,
  min(entities.first_seen_at) AS first_seen_at,
  max(entities.last_seen_at) AS last_seen_at
FROM reporting.business_entities AS entities
GROUP BY entities.entity_type
ORDER BY entity_count DESC, entities.entity_type;
```

This includes recognized shipments, quotes, bookings, and consols without merging them into one
business fact. `entity_type` keeps their meaning explicit.

Add subtype business numbers only when needed:

```sql theme={null}
SELECT
  entities.entity_id,
  entities.entity_type,
  shipments.job_number,
  quotes.quote_number,
  bookings.booking_number,
  consols.consol_number
FROM reporting.business_entities AS entities
LEFT JOIN reporting.business_shipments AS shipments
  ON shipments.entity_id = entities.entity_id
LEFT JOIN reporting.business_one_off_quotes AS quotes
  ON quotes.entity_id = entities.entity_id
LEFT JOIN reporting.business_bookings AS bookings
  ON bookings.entity_id = entities.entity_id
LEFT JOIN reporting.business_consols AS consols
  ON consols.entity_id = entities.entity_id
ORDER BY entities.entity_type, entities.entity_key;
```

Only the matching subtype relation contributes a business number.

## Example 2: Analyze charge lines

This query starts from `entity_charges` because the result measures charges, not entities:

```sql theme={null}
SELECT
  entities.entity_type,
  charges.charge_code,
  charges.charge_description,
  charges.sell_overseas_currency_code,
  count(*) AS charge_line_count,
  sum(charges.sell_overseas_amount) AS sell_overseas_amount
FROM reporting.entity_charges AS charges
JOIN reporting.business_entities AS entities
  ON entities.entity_id = charges.entity_id
WHERE charges.sell_is_posted IS TRUE
  AND charges.sell_overseas_amount IS NOT NULL
  AND charges.sell_overseas_currency_code IS NOT NULL
GROUP BY
  entities.entity_type,
  charges.charge_code,
  charges.charge_description,
  charges.sell_overseas_currency_code
ORDER BY
  entities.entity_type,
  charges.sell_overseas_currency_code,
  sell_overseas_amount DESC;
```

The currency remains part of the grouping. Do not sum amounts across currencies.

Add entity-level information without changing the charge grain:

```sql theme={null}
SELECT
  charges.charge_id,
  charges.entity_id,
  entities.entity_type,
  entities.entity_key,
  charges.charge_code,
  charges.debtor_code,
  charges.creditor_code,
  charges.sell_local_amount,
  charges.cost_local_amount,
  charges.sell_is_posted,
  charges.cost_is_posted
FROM reporting.entity_charges AS charges
JOIN reporting.business_entities AS entities
  ON entities.entity_id = charges.entity_id
WHERE charges.charge_code = 'FRT'
ORDER BY entities.entity_type, entities.entity_key, charges.sequence;
```

`FRT` is illustrative. Inspect your organization's observed codes before creating a recurring
query.

## Example 3: Traverse explicit relationships

This query resolves both endpoints through `entity_identities`. It keeps the observer separate:

```sql theme={null}
SELECT
  relationships.relationship_type,
  from_identity.entity_type AS from_entity_type,
  from_identity.entity_key AS from_entity_key,
  from_identity.is_reference_only AS from_is_reference_only,
  to_identity.entity_type AS to_entity_type,
  to_identity.entity_key AS to_entity_key,
  to_identity.is_reference_only AS to_is_reference_only,
  observer.entity_type AS observing_entity_type,
  observer.entity_key AS observing_entity_key,
  relationships.evidence_basis,
  relationships.source_reference_name,
  relationships.source_reference_value,
  relationships.observed_at
FROM reporting.entity_relationships AS relationships
JOIN reporting.entity_identities AS from_identity
  ON from_identity.entity_id = relationships.from_entity_id
JOIN reporting.entity_identities AS to_identity
  ON to_identity.entity_id = relationships.to_entity_id
JOIN reporting.business_entities AS observer
  ON observer.entity_id = relationships.observing_entity_id
WHERE relationships.relationship_type IN (
  'quote_to_shipment',
  'quote_to_booking',
  'quote_to_consol'
)
ORDER BY relationships.observed_at DESC;
```

One logical relationship can have several evidence rows. Deduplicate the endpoint pair when the
question needs one business edge:

```sql theme={null}
SELECT DISTINCT
  relationships.from_entity_id,
  relationships.to_entity_id,
  relationships.relationship_type
FROM reporting.entity_relationships AS relationships
WHERE relationships.relationship_type = 'quote_to_shipment';
```

To traverse a packing-line reference to its resolved container:

```sql theme={null}
SELECT
  relationships.from_entity_id,
  packing_lines.packing_line_id,
  containers.container_number,
  relationships.evidence_basis,
  relationships.resolution_scope
FROM reporting.entity_component_relationships AS relationships
JOIN reporting.entity_packing_lines AS packing_lines
  ON packing_lines.component_id = relationships.from_component_id
JOIN reporting.entity_containers AS containers
  ON containers.component_id = relationships.to_component_id
WHERE relationships.relationship_type = 'packing_line_to_container'
  AND relationships.resolution_status = 'resolved';
```

The component query uses the explicit component edge. It does not join a packing line to a
container because their displayed container numbers happen to match.

## Filter entities without multiplying them

Use one `EXISTS` block for each repeating relation that only determines eligibility:

```sql theme={null}
SELECT
  entities.entity_type,
  count(*) AS entity_count,
  sum(entities.total_weight_kg) AS total_weight_kg
FROM reporting.business_entities AS entities
WHERE EXISTS (
  SELECT 1
  FROM reporting.entity_dates AS dates
  WHERE dates.entity_id = entities.entity_id
    AND dates.date_type_code = 'ETD'
    AND dates.date_value_local >= '2026-04-01'
    AND dates.date_value_local < '2026-05-01'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_staff_assignments AS staff
  WHERE staff.entity_id = entities.entity_id
    AND staff.staff_role = 'sales'
)
AND EXISTS (
  SELECT 1
  FROM reporting.entity_charges AS charges
  WHERE charges.entity_id = entities.entity_id
    AND charges.sell_is_posted IS TRUE
)
GROUP BY entities.entity_type;
```

Replace the example date and role codes with values observed in your data.

## Pre-aggregate detail measures

If a result needs both entity and detail measures, reduce the detail to one row per `entity_id`
before joining:

```sql theme={null}
WITH posted_sell AS (
  SELECT
    entity_id,
    sell_overseas_currency_code,
    sum(sell_overseas_amount) AS sell_overseas_amount
  FROM reporting.entity_charges
  WHERE sell_is_posted IS TRUE
    AND sell_overseas_amount IS NOT NULL
    AND sell_overseas_currency_code IS NOT NULL
  GROUP BY entity_id, sell_overseas_currency_code
)
SELECT
  entities.entity_type,
  posted_sell.sell_overseas_currency_code,
  count(*) AS entity_currency_rows,
  sum(posted_sell.sell_overseas_amount) AS sell_overseas_amount
FROM reporting.business_entities AS entities
JOIN posted_sell
  ON posted_sell.entity_id = entities.entity_id
GROUP BY
  entities.entity_type,
  posted_sell.sell_overseas_currency_code
ORDER BY
  entities.entity_type,
  posted_sell.sell_overseas_currency_code;
```

The result grain is one entity type and currency combination.

## Inspect codes before filtering

CargoWise roles and codes can differ by organization:

```sql theme={null}
SELECT
  'entity_type' AS field,
  entity_type AS value,
  count(*) AS row_count
FROM reporting.business_entities
GROUP BY entity_type

UNION ALL

SELECT
  'date_type_code',
  date_type_code,
  count(*)
FROM reporting.entity_dates
WHERE date_type_code IS NOT NULL
GROUP BY date_type_code

UNION ALL

SELECT
  'staff_role',
  staff_role,
  count(*)
FROM reporting.entity_staff_assignments
WHERE staff_role IS NOT NULL
GROUP BY staff_role

ORDER BY field, row_count DESC, value;
```

Inspect descriptions and names when a code is unfamiliar. Use stable source codes in recurring
queries after confirming their meaning.

## Validate the result

Before sharing an answer, check:

* the result still has the intended grain
* every join uses a documented key
* entity relationships come from explicit edges
* codes and roles mean what the business question expects
* currencies are not mixed
* normalized units are populated for measured rows
* local dates and UTC dates are not mixed without a rule
* null and unsupported values remain visible
* a few known CargoWise records produce the expected result

If the required value is not in a typed relation, continue to
[Discover fields](/analytics/discover-fields).