AMQL Docs
Cookbook

Nested Aggregation

A worked cookbook example: aggregate an order-level metric up to the user grain, then take the max per country.

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

Query max_user_aov alongside cities.country_name:

explore {
  dimensions { cities.country_name }
  measures { max_user_aov }
}

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

Customer Order Frequency

Build a cumulative order-frequency chart in AQL using dimensionalize, percent-of-total, and running_total to show how item counts distribute across orders.

Ranking

Rank items in AQL using rank() and ntile(): globally, within partitions, as a fixed dimension, or grouped into performance tiers such as quartiles or deciles.

On this page

The questionModels and relationshipsStep 1: the inner metric (AOV)Step 2: nest it at the user grain, then take the maxStep 3: use it with a coarser dimensionSee also