The Problem

I want to make a weighted average.

The Solution

In this example we will make a very basic “customer health” score, that takes the average of the customer’s order prices, weighted by when the order was placed:

• We give to each order a numerical weight, giving a higher weight for a more recent order, as shown in the example below:

dimension: weight {
  type: number
  sql:
    CASE
      WHEN ${days_since_order} < 30 THEN 3
      WHEN ${days_since_order} < 60 THEN 2
      ELSE 1
    END ;;
}

• From this, we can make a weighted price, by multiplying the weight by the price, and finally taking the average of this weighted price.

dimension: weighted_price {
  type: number
  sql: ${sale_price} * ${weight} ;;
}

measure: weighted_average {
  type: average
  sql: ${weighted_price} ;;
}

• The result is a weighted average, which focuses on recent orders: