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.