# 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](/as-code/aql/learn/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

```aml
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:

```aml
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

```aml
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](/as-code/aql/learn/nested-aggregation): the concept and the simpler "average monthly signups" example
- [Learn AQL → Cross-Model Queries](/as-code/aql/learn/cross-model): how AQL resolves the order_items → users → cities chain