Nested Aggregations


Some metrics are simple to calculate like the Total Revenue, Number of Active Users, or Average Price. These can typically be implemented with a single aggregation and perhaps a filter:

  • Total Revenue: SELECT SUM(Revenue)
  • Number of Active Users: SELECT COUNT(User) WHERE User Status = Active
  • Average Price: SELECT AVG(Price)

In MAQL syntax:

  • SELECT SUM({fact/revenue})
  • SELECT COUNT({attribute/user}) WHERE {label/user_status} = "Active"
  • SELECT AVG({fact/price})

However, metrics can become more complex when multiple aggregations are needed. For example, calculating the average monthly revenue requires summing the revenue for each month before calculating the average. Similarly, calculating the average number of active users per quarter involves counting the active users for each quarter and then averaging that count.

For such cases, we need to use nested aggregations.