Nested Aggregation

A focused recipe for nesting aggregations across multiple models. For the underlying concept, see Learn AQL → Nested Aggregation.

The question

Which country has the user with the highest Average Order Value (AOV)?

Three grains are in play:

• Order item (order_items): the raw fact rows.
• User (users): we aggregate AOV per user.
• Country (cities.country_name): we take the max user AOV per country.

Models and relationships

Model order_items {
  dimension id {...}
  dimension user_id {...}
  dimension product_id {...}
  dimension quantity {...}
}

Model products { dimension id {...} dimension price {...} }
Model users    { dimension id {...} dimension city_id {...} }
Model cities   { dimension id {...} dimension country_name {...} }

Dataset e_commerce {
  models: [order_items, products, users, cities]
  relationships: [
    relationship(order_items.user_id > users.id, true),
    relationship(order_items.product_id > products.id, true),
    relationship(users.city_id > cities.id, true),
  ]
}

Step 1: the inner metric (AOV)

AOV is total order value divided by distinct order count:

metric aov {
  label: 'AOV'
  type: 'number'
  definition: @aql
    sum(order_items, order_items.quantity * products.price)
    / count_distinct(order_items.order_id)
  ;;
}

Step 2: nest it at the user grain, then take the max

metric max_user_aov {
  label: 'Max User AOV'
  type: 'number'
  definition: @aql
    order_items
    | group(users.id)
    | select(aov)
    | max()
  ;;
}

Walking through:

1. order_items: start from order items, the finest grain.
2. | group(users.id): bucket by user.
3. | select(aov): compute AOV per user.
4. | max(): take the largest of those per-user AOVs.

Step 3: use it with a coarser dimension

In a report, drop max_user_aov alongside cities.country_name:

Because nested aggregation respects surrounding context, AQL evaluates the inner per-user AOV within each country and returns the country-level max. Same metric, no rewriting needed for new dimensions.

See also

• Learn AQL → Nested Aggregation: the concept and the simpler "average monthly signups" example
• Learn AQL → Cross-Model Queries: how AQL resolves the order_items → users → cities chain