Holistics AQL learning Lesson 0003

AQLearn 1.2 / Function

Aggregate Functions

Source lesson: AQLearn 1.2: Function: Aggregate Functions. Tangible win: choose the right aggregate function for a measure.

Why this matters for your mission: metrics are built from aggregate functions. If you can name the aggregate correctly, you can model and debug basic measures quickly.

Core idea

An aggregate function turns many field values into one summary value. If your explore has dimensions, the aggregate is calculated once per dimension group.

Syntax

model | aggregate_func(model.field) 
orders | count(orders.id)

Four functions to memorize first

count()

Counts values. Common for order count, user count, row count.

orders | count(orders.id)

max()

Returns highest value. For timestamps, highest usually means latest.

orders | max(orders.created_at)

min()

Returns lowest value. For timestamps, lowest usually means oldest.

orders | min(orders.created_at)

avg()

Returns average numeric value. Good for discount, age, duration.

orders | avg(orders.discount)

AQLearn exercise target

AQLearn asks you to extend count by status with latest order time, oldest order time, and average discount.

explore {
  measures {
    count_of_orders: orders | count(orders.id),
    latest_order_time: orders | max(orders.created_at),
    oldest_order_time: orders | min(orders.created_at),
    avg_order_discount: orders | avg(orders.discount),
  }
  dimensions {
    order_status: orders.status,
  }
}
Customer-safe explanation: “For each order status, this query shows order count, latest order timestamp, oldest order timestamp, and average discount.”

Retrieval practice

1. Which function gets the latest orders.created_at per status?
2. Which function gets the oldest orders.created_at per status?
3. Which function gets average order discount?
4. Because the explore dimension is orders.status, each measure returns…

Do in AQLearn

  1. Open AQLearn Aggregate Functions.
  2. Add latest_order_time: orders | max(orders.created_at),.
  3. Add oldest_order_time: orders | min(orders.created_at),.
  4. Add avg_order_discount: orders | avg(orders.discount),.
  5. Run after each step and read the result by order status.

Next local lesson

Next: dimensions and measures, why dimensions control the level of detail of each aggregate.