The Problem

I want to make a weighted average. In this example I’ll make a very basic “customer health” score that takes the average of the customer’s order prices, weighted by when the order was placed.

The Solution

In this example, I’ll give each order a numerical weight, giving a higher weight for a more recent order.

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, I 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.