> For the complete documentation index, see [llms.txt](https://docs.mediarithmics.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.mediarithmics.io/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-examples.md).
# OTQL examples
Worked OTQL queries, from how resolution works to the ones that look right and are not.
## Your goal
See the language applied to real questions - and recognise the shapes that produce a wrong answer without failing.
## How a query is resolved
OTQL syntax is close to SQL, but the execution is not: it works on **object trees**, not columns. Resolution is a **two-phase** process.
For these examples, take this runtime schema:
```graphql
type UserPoint {
id: ID!
activities: [UserActivity!]!
profiles: [UserProfile!]!
}
type UserActivity {
id: ID!
events: [UserEvent!]!
}
type UserEvent {
id: ID!
name: String
}
type UserProfile {
id: ID!
age: String
}
```
{% stepper %}
{% step %}
### Narrow the queried object
The `WHERE` clause is applied to the object named in `FROM`, and to any sub-object's fields.
```sql
SELECT { activities { events { name } } }
FROM UserPoint
WHERE activities { events { name = "$transaction_confirmed" } }
```
{% hint style="warning" %}
**When the `WHERE` clause is applied to a sub-object's field, one matching sub-object validates all its parents.** One event with `name = "$transaction_confirmed"` selects its activity, and therefore its UserPoint.
{% endhint %}
{% endstep %}
{% step %}
### Return the desired objects and fields
Only what is listed in the `SELECT` clause is returned.
The query above means "get all event names, for users with at least one confirmed transaction" - so the result contains **all** the events of those users, not only the transactions:
```json
[
{
"activities": [
{ "events": [ { "name": "$page_view" } ] },
{ "events": [ { "name": "$page_view" }, { "name": "$transaction_confirmed" } ] }
]
}
]
```
{% endstep %}
{% endstepper %}
{% hint style="danger" %}
This is the single most common misreading of OTQL. `WHERE` selects **users**; it does not filter what `SELECT` returns for them. To restrict the returned objects, change the scope with `FROM`, or use `@filter`.
{% endhint %}
## Changing the scope of a query
The same underlying question, asked from three starting points:
```sql
-- All event names, for users with at least one confirmed transaction
SELECT { activities { events { name } } }
FROM UserPoint
WHERE activities { events { name = "$transaction_confirmed" } }
-- Only the transaction events themselves
SELECT { name }
FROM UserEvent
WHERE name = "$transaction_confirmed"
-- Transaction events, restricted to users aged between 20 and 30
SELECT { name }
FROM UserEvent
WHERE name = "$transaction_confirmed"
JOIN UserPoint WHERE profiles { age >= 20 AND age <= 30 }
```
Pick the `FROM` that matches what you want **counted or returned**, then use `WHERE` and `JOIN` for the conditions.
## Scoring: monetary and frequency conditions
For these examples, take this schema:
```graphql
type UserPoint @TreeIndexRoot(index:"USER_INDEX") {
id: ID!
activity_events: [ActivityEvent!]!
}
type ActivityEvent @Mirror(object_type:"UserEvent") {
order: Order @Property(path:"$properties.order")
}
type Order {
order_products: [OrderProduct]!
date: Timestamp!
}
type OrderProduct {
id: String @TreeIndex(index:"USER_INDEX")
price: Float @TreeIndex(index:"USER_INDEX")
category: String @TreeIndex(index:"USER_INDEX")
}
```
**The question: count UserPoints who bought more than €1,000 of IT products.** The answer depends on what "more than €1,000" means.
{% tabs %}
{% tab title="In a single order" %}
```sql
SELECT @count{} FROM UserPoint
WHERE activity_events {
order {
order_products @ScoreField(name: "price") @ScoreSum(min: 1000) {
category="IT"
}
}
}
```
The threshold is applied **inside one order**: the sum of IT product prices in a single order must exceed 1,000.
{% endtab %}
{% tab title="Across orders" %}
```sql
-- Implicit form
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
order {
order_products @ScoreField(name:"price") {
category="IT"
}
}
}
```
The threshold moves **up** to `activity_events`, so the sum is across all orders. The explicit form makes the two score roles visible:
```sql
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000, result:"boolean_value") {
order {
order_products @ScoreField(name:"price") @ScoreSum(result:"score_value") {
category="IT"
}
}
}
```
{% endtab %}
{% tab title="Across orders, last 10 days" %}
```sql
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
order {
order_products @ScoreField(name:"price") {
category="IT"
}
AND date > "now-10d"
}
}
```
The date condition sits on the `order`, next to the products - not on the outer scoring.
{% endtab %}
{% tab title="With a per-product floor" %}
```sql
-- More than €1,000 across orders, counting only products of at least €10
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
order {
order_products @ScoreField(name: "price") @ScoreSum(min: 10, result:"score_value") {
category="IT"
}
}
}
```
{% endtab %}
{% tab title="With a product count" %}
```sql
-- More than €1,000 across orders, and at least 10 IT products
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
order {
order_products @ScoreField(name: "price") @ScoreSum(result:"score_value") {
category="IT"
}
AND order_products @ScoreSum(min: 10) {
category="IT"
}
}
}
```
Two conditions on the same collection: one summing prices, one counting items. Note that the second has **no** `@ScoreField`, so its score is a count.
{% endtab %}
{% tab title="With a maximum" %}
```sql
-- More than €1,000 across orders, with at least one product over €10
SELECT @count {} FROM UserPoint
WHERE activity_events @ScoreSum(min : 1000) {
order {
order_products @ScoreField(name: "price") @ScoreSum(result:"score_value") {
category="IT"
}
AND order_products @ScoreField(name: "price") @ScoreMax(min: 10) {
category="IT"
}
}
}
```
`@ScoreMax` with `@ScoreField` is how you express "at least one item worth more than X" - as opposed to `@ScoreSum`, which would test the total.
{% endtab %}
{% endtabs %}
{% hint style="warning" %}
Some combinations of nested scoring do **not** express what they appear to. A query intended as "more than €1,000 across orders, with at least 10 orders over €100" can instead evaluate as "more than 1,000 orders with at least 10 orders over €100" - a completely different population, returned without error.
When a scoring query nests more than two levels, verify it against a known population before you build a segment on it.
{% endhint %}
## Date queries
```graphql
type UserPoint @TreeIndexRoot(index:"USER_INDEX") {
id: ID!
profiles: [UserProfile!]!
}
type UserProfile {
id: ID!
birth_date: Date @TreeIndex(index:"USER_INDEX")
}
```
**Users celebrating their birthday today, aged 18 to 28** - one rounded anchor per rolling year:
```sql
SELECT { id }
FROM UserPoint
WHERE profiles { birth_date IN ["now-18y/d", "now-19y/d", "now-20y/d", "now-21y/d",
"now-22y/d", "now-23y/d", "now-24y/d", "now-25y/d", "now-26y/d", "now-27y/d", "now-28y/d"]
}
```
**Users celebrating their birthday in the next 7 days, aged 18 to 28** - a range per year:
```sql
SELECT { id }
FROM UserPoint
WHERE profiles {
( birth_date >= "now-18y/d" AND birth_date < "now+7d-18y" ) OR
( birth_date >= "now-19y/d" AND birth_date < "now+7d-19y" ) OR
( birth_date >= "now-20y/d" AND birth_date < "now+7d-20y" )
-- ... one clause per year up to 28
}
```
{% hint style="warning" %}
`now` is evaluated **at the start of the segment calculation**, which can produce a discrepancy between the expected and the actual values. Rounding with `/d` reduces the effect.
{% endhint %}
## Filtering what is returned
`@filter` restricts the objects returned by `SELECT`, which is exactly what `WHERE` does not do.
```graphql
type UserPoint @TreeIndexRoot(index:"USER_INDEX") {
id: ID!
events: [UserEvent!]!
activities: [UserActivity!]!
}
type UserActivity {
id: ID!
events: [UserEvent!]!
}
type UserEvent @Mirror(object_type:"UserEvent") {
id: ID!
event_name: String @Property(path:"$event_name") @TreeIndex(index:"USER_INDEX")
}
```
**All URLs of type `newsarticle` and category `actu` browsed yesterday, per user:**
```sql
SELECT { events @filter(clause: "page_type == \"newsarticle\" AND page_category == \"actu\""){ url } }
FROM UserPoint
WHERE activities { ts >= "now-1d/d" }
```
{% hint style="info" %}
The result contains **empty arrays** for users who matched the `WHERE` but have no event matching the filter. That is expected: the `WHERE` selected the user, the `@filter` selected the events.
Remember that `@filter` in `SELECT` is **not compatible with aggregation operations** - see [OTQL documentation](/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-documentation.md#aggregation-operations).
{% endhint %}
## Before you build a segment on a query
{% stepper %}
{% step %}
### Run it in the Query Tool
And read the number, not just the fact that it returned. See [Query Tool](/setup-and-monitor/operate-and-monitor/query-your-datamart/query-tool.md).
{% endstep %}
{% step %}
### Sanity-check the magnitude
Compare against a count you already know - total UserPoints, or the same query with one condition removed. A scoring query that means something other than you intended usually returns a suspiciously round or suspiciously large number.
{% endstep %}
{% step %}
### Check a single user
Take one UserPoint the query returns and read their timeline. See [Explore a user profile](/setup-and-monitor/operate-and-monitor/explore-a-user-profile.md).
{% endstep %}
{% step %}
### Remember what changes in a segment
`LIMIT` is ignored during segment calculation, and `now` is evaluated when the calculation starts.
{% endstep %}
{% endstepper %}
## Related pages
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.
Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:
```
GET https://docs.mediarithmics.io/setup-and-monitor/operate-and-monitor/query-your-datamart/otql/otql-examples.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.