Nested aggregation

Some questions are aggregations of aggregations:

• "Average monthly new customers": first count per month, then average those counts.
• "Best month for revenue": first sum per month, then take the max.
• "Median customer's lifetime value": first sum per customer, then take the median.

In SQL you'd use a subquery or CTE. In AQL, you do it inline with group() + select() wrapped in another aggregator.

Try it interactively

Practice in the AQL Playground: Nested Aggregations (1) · Nested Aggregations (2) · Nested Aggregations (3).

The pattern

<outer aggregator>(
  <source table>
  | group(<inner dimension>)
  | select(<inner aggregation>)
)

Read it bottom-up: group by something, calculate per group, then aggregate the groups.

Example: average monthly new customers

First a regular base metric:

metric new_customers {
  definition: @aql count(users.id) ;;
}

Now nest it:

metric avg_monthly_acquisition {
  definition: @aql
    users
    | group(users.created_at | month())
    | select(new_customers)
    | average()
  ;;
}

Walking through:

1. users: start from the users table.
2. | group(users.created_at | month()): bucket by month.
3. | select(new_customers): count customers in each month.
4. | average(): average those monthly counts.

Notice the metric has no year-specific logic. Drop it into a report grouped by year and you get the average monthly acquisition for each year. Drop it into a report grouped by country and you get average monthly acquisition per country. Same metric, adapts to context (the usual AQL story).

When you need this vs LoD

Question	Tool
Percent of total	of_all()
Aggregation of aggregations	Nested (this page)
Use an aggregation as a dimension	dimensionalize()

A rough rule: if the outer step needs to see all the inner results as separate numbers (to average, max, median, etc.), it's nested. If the outer step just needs the total ignoring some dimension, it's LoD.

More examples

• Cookbook: Max user AOV by country: a cross-model nesting walkthrough (order items → user grain → country max).

Next

→ Time comparisons: the last big context modifier.