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

# Querying

> Address tables, read types and JSON columns in ClickHouse and BigQuery, and rebuild a customer from the tables.

Replace `<catalog>` and `<namespace>` (or `<project>` and `<dataset>` on BigQuery) with the names Autumn sent you. Examples on the other v2.6 pages are written as plain SQL against `<namespace>.v2_6_<object>`; this page shows how each engine spells them.

## Addressing tables

> [tab] ClickHouse
>
> The Glue catalog mounts as a database, and the whole `<namespace>.v2_6_<object>` is one table name with a literal dot in it. Backtick **both** parts:
>
> ```sql theme={null}
> SELECT * FROM `<catalog>`.`<namespace>.v2_6_customers` LIMIT 10;
> ```
>
> `` `<catalog>.<namespace>`.`v2_6_customers` `` does not resolve, and neither does the single-quoted form.

> [tab] BigQuery
>
> Tables are ordinary `project.dataset.table` names:
>
> ```sql theme={null}
> SELECT * FROM `<project>.<dataset>.v2_6_customers` LIMIT 10;
> ```

## Types

Every v2.6 column is one of five types. There are no array, map or struct columns.

| Type in the docs | ClickHouse | BigQuery |
| - | - | - |
| `string` | `Nullable(String)` | `STRING` |
| `string (JSON)` | `Nullable(String)` | `STRING` |
| `boolean` | `Nullable(Bool)` | `BOOL` |
| `long`, `long (epoch ms)` | `Nullable(Int64)` | `INT64` |
| `decimal` | `Nullable(Decimal(38, 10))` | `BIGNUMERIC` |

`events.timestamp` is the one native timestamp.

## Timestamps

Columns typed `long (epoch ms)` (`created_at`, `started_at`, `expires_at`, `current_period_end`, `as_of` and the like) count milliseconds since 1970-01-01 UTC. Convert them before you display or compare with dates:

> [tab] ClickHouse
>
> ```sql theme={null}
> SELECT customer_id, fromUnixTimestamp64Milli(created_at) AS created
> FROM `<catalog>`.`<namespace>.v2_6_customers`;
>
> -- "now" in epoch ms
> SELECT toUnixTimestamp64Milli(now64());
> ```

> [tab] BigQuery
>
> ```sql theme={null}
> SELECT customer_id, TIMESTAMP_MILLIS(created_at) AS created
> FROM `<project>.<dataset>.v2_6_customers`;
>
> -- "now" in epoch ms
> SELECT UNIX_MILLIS(CURRENT_TIMESTAMP());
> ```

## JSON columns

Objects and arrays are stored as JSON text in string columns, in the same shape the API returns. Columns typed `string (JSON)` in the [Schema Reference](https://docs.useautumn.com/documentation/lakehouse/v2-6/schema.md) hold either:

* an **object**, such as `v2_6_customers.metadata = '{"region":"eu"}'` or `v2_6_features.display = '{"singular":"message","plural":"messages"}'`, or
* an **array**, such as `v2_6_features.event_names = '["message_sent","message_edited"]'`, `v2_6_invoices.plan_ids = '["pro","seats"]'`, `v2_6_plan_items.price_tiers` or `events.deductions`.

Each engine below shows the same four operations: read a field of an object, check whether an array contains a value, turn an array into rows, and read fields from an array of objects.

> [tab] ClickHouse
>
> ```sql theme={null}
> -- Read a field of an object
> SELECT customer_id, JSONExtractString(metadata, 'region') AS region
> FROM `<catalog>`.`<namespace>.v2_6_customers`;
>
> -- Does an array contain a value?
> SELECT feature_id
> FROM `<catalog>`.`<namespace>.v2_6_features`
> WHERE has(JSONExtract(ifNull(event_names, '[]'), 'Array(String)'), 'message_sent');
>
> -- One row per array element
> SELECT invoice_id, arrayJoin(JSONExtract(ifNull(plan_ids, '[]'), 'Array(String)')) AS plan_id
> FROM `<catalog>`.`<namespace>.v2_6_invoices`;
>
> -- Fields from an array of objects: what each event deducted
> SELECT
>   e.id,
>   JSONExtractString(d, 'balance_id') AS balance_id,
>   JSONExtractString(d, 'feature_id') AS feature_id,
>   JSONExtractFloat(d, 'value')       AS value
> FROM `<catalog>`.`<namespace>.events` AS e
> ARRAY JOIN JSONExtractArrayRaw(ifNull(e.deductions, '[]')) AS d;
> ```
>
> The columns are `Nullable(String)`, and ClickHouse won't build an array from a nullable value, so wrap them in `ifNull(col, '[]')` before `JSONExtract(..., 'Array(...)')` or `JSONExtractArrayRaw`.

> [tab] BigQuery
>
> ```sql theme={null}
> -- Read a field of an object
> SELECT customer_id, JSON_VALUE(metadata, '$.region') AS region
> FROM `<project>.<dataset>.v2_6_customers`;
>
> -- Does an array contain a value?
> SELECT feature_id
> FROM `<project>.<dataset>.v2_6_features`
> WHERE 'message_sent' IN UNNEST(JSON_VALUE_ARRAY(event_names));
>
> -- One row per array element
> SELECT invoice_id, plan_id
> FROM `<project>.<dataset>.v2_6_invoices`,
>   UNNEST(JSON_VALUE_ARRAY(plan_ids)) AS plan_id;
>
> -- Fields from an array of objects: what each event deducted
> SELECT
>   e.id,
>   JSON_VALUE(d, '$.balance_id')                  AS balance_id,
>   JSON_VALUE(d, '$.feature_id')                  AS feature_id,
>   CAST(JSON_VALUE(d, '$.value') AS FLOAT64)      AS value
> FROM `<project>.<dataset>.events` AS e,
>   UNNEST(JSON_QUERY_ARRAY(e.deductions)) AS d;
> ```

> [!NOTE]
>
> Price tiers end with `"to": "inf"` (a string) on the last tier, and a number on the others. Read `to` as text, then convert the numeric ones.

## Nulls in ClickHouse

On Iceberg tables, ClickHouse can raise `NOT_FOUND_COLUMN_IN_BLOCK` for `WHERE col IS NULL`. If you hit it, write `coalesce(col, '') = ''` for strings and `coalesce(col, 0) = 0` for numbers instead. BigQuery handles `IS NULL` normally.

## Rebuild a customer

Each part of `POST /v1/customers.get` comes from one table. Resolve the customer's internal id once, then read each part with it. Join on `org_id` and `env` as well when you query more than one environment.

| Part of the customer | Table and filter |
| - | - |
| Top-level fields | `v2_6_customers` |
| `subscriptions[]` | `v2_6_subscriptions`, customer-level, not seats, live statuses ([details](https://docs.useautumn.com/documentation/lakehouse/v2-6/schema.md#the-apis-status-of-a-subscription)), `ORDER BY position` |
| `purchases[]` | `v2_6_purchases`, same filter, `ORDER BY position` |
| `balances{}` | `v2_6_balances` with `scope = 'customer'` |
| `balances{}.breakdown[]` | `v2_6_breakdowns` on `balance_id`, ordered by `position` |
| `balances{}.rollovers[]` | `v2_6_rollovers` on `balance_id` |
| `flags{}` | `v2_6_flags` with `scope = 'customer'` |
| `licenses[]` | `v2_6_licenses` |
| `entities[]` | `v2_6_entities` |
| `invoices[]` | `v2_6_invoices`, newest 10 |
| `referrals[]` | `v2_6_referrals` |

An entity (`POST /v1/entities.get`) works the same way with the entity's view: `scope = 'entity'` and its `internal_entity_id`.

The balances part, in each engine:

> [tab] ClickHouse
>
> ```sql theme={null}
> SELECT b.feature_id, b.granted, b.remaining, b.usage, b.unlimited, b.next_reset_at
> FROM `<catalog>`.`<namespace>.v2_6_balances` AS b
> INNER JOIN `<catalog>`.`<namespace>.v2_6_customers` AS c
>   ON c.org_id = b.org_id AND c.env = b.env
>  AND c.internal_customer_id = b.internal_customer_id
> WHERE b.env = 'live' AND c.customer_id = 'cus_123' AND b.scope = 'customer';
> ```

> [tab] BigQuery
>
> ```sql theme={null}
> SELECT b.feature_id, b.granted, b.remaining, b.usage, b.unlimited, b.next_reset_at
> FROM `<project>.<dataset>.v2_6_balances` AS b
> JOIN `<project>.<dataset>.v2_6_customers` AS c
>   ON c.org_id = b.org_id AND c.env = b.env
>  AND c.internal_customer_id = b.internal_customer_id
> WHERE b.env = 'live' AND c.customer_id = 'cus_123' AND b.scope = 'customer';
> ```

### Trials a customer has used

`trials_used[]` has no table of its own. A trial was used when a subscription had a trial and did not stay `pending`. The API also counts trials used by other customers with the same `fingerprint`:

```sql theme={null}
SELECT s.plan_id, c.customer_id, c.fingerprint
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_customers c
  ON c.org_id = s.org_id AND c.env = s.env AND c.internal_customer_id = s.internal_customer_id
WHERE s.env = 'live'
  AND (c.internal_customer_id = '<internal_customer_id>'
       OR c.fingerprint = (SELECT fingerprint FROM <namespace>.v2_6_customers
                           WHERE env = 'live' AND internal_customer_id = '<internal_customer_id>'))
  AND s.trial_ends_at IS NOT NULL
  AND s.status <> 'pending'
  AND c.customer_id IS NOT NULL;
```

### What the lakehouse doesn't have

Three parts of the customer are fetched from Stripe or computed when you call the API, so they are in no lakehouse version: `rewards.discounts[]`, `invoice_previews[]`, and the payment method and billing details.

## Examples

### Active subscriptions with their plan

```sql theme={null}
SELECT s.customer_id, s.plan_id, p.name, p.price_amount, p.price_interval, s.current_period_end
FROM <namespace>.v2_6_subscriptions s
JOIN <namespace>.v2_6_plans p
  ON p.org_id = s.org_id AND p.env = s.env AND p.internal_plan_id = s.internal_plan_id
WHERE s.env = 'live' AND s.status IN ('active', 'past_due');
```

### Customers close to running out

```sql theme={null}
SELECT customer_id, feature_id, granted, remaining,
       remaining / nullif(granted, 0) AS share_left
FROM <namespace>.v2_6_balances
WHERE env = 'live' AND scope = 'customer' AND NOT unlimited AND granted > 0
ORDER BY share_left
LIMIT 50;
```

### Usage per day, by plan

`events.deductions` records which plan each unit of usage came from:

> [tab] ClickHouse
>
> ```sql theme={null}
> SELECT
>   toDate(e.timestamp)                         AS day,
>   JSONExtractString(d, 'plan_id')             AS plan_id,
>   sum(JSONExtractFloat(d, 'value'))           AS usage
> FROM `<catalog>`.`<namespace>.events` AS e
> ARRAY JOIN JSONExtractArrayRaw(ifNull(e.deductions, '[]')) AS d
> WHERE e.env = 'live' AND JSONExtractString(d, 'feature_id') = 'messages'
> GROUP BY day, plan_id
> ORDER BY day;
> ```

> [tab] BigQuery
>
> ```sql theme={null}
> SELECT
>   DATE(e.timestamp)                                AS day,
>   JSON_VALUE(d, '$.plan_id')                       AS plan_id,
>   SUM(CAST(JSON_VALUE(d, '$.value') AS FLOAT64))   AS usage
> FROM `<project>.<dataset>.events` AS e,
>   UNNEST(JSON_QUERY_ARRAY(e.deductions)) AS d
> WHERE e.env = 'live' AND JSON_VALUE(d, '$.feature_id') = 'messages'
> GROUP BY day, plan_id
> ORDER BY day;
> ```

## Cross-database joins

Join lakehouse tables against your own data in the same engine by qualifying both sides. Join on your own customer id (`customer_id`), which is the id you share with Autumn, and filter early by `env` and time range on large tables such as `events`.

```sql theme={null}
-- ClickHouse
SELECT c.customer_id, c.email, u.signup_source
FROM `<catalog>`.`<namespace>.v2_6_customers` AS c
INNER JOIN my_db.users AS u ON u.autumn_customer_id = c.customer_id
WHERE c.env = 'live';
```

## Important notes

* **Join on internal ids.** Inside the lakehouse, join on `internal_*` ids, `customer_plan_id` and `balance_id`, never on `customer_id`, `plan_id` or `entity_id`. Your own ids can be renamed, can be null (`customer_id`), are shared by every version of a plan (`plan_id`), or are unique only within one customer (`entity_id`).
* **Filter `env`.** `live` and `sandbox` rows sit in the same tables.
* **Pick a balance view.** See [Working with balances](https://docs.useautumn.com/documentation/lakehouse/v2-6/balances.md#1-one-row-per-view).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.