So this is the original query:

```
{
"size": 0,
"query": {
"bool": {
"must": [
{
"range": {
"report_date": {
"gte": "2021-01-01",
"lte": "2021-12-31"
}
}
},
{
"bool": {
"minimum_should_match": 1,
"should": [
{
"term": {
"segment": {
"value": "null"
}
}
},
{
"bool": {
"must_not": [
{
"exists": {
"field": "segment"
}
}
]
}
}
]
}
}
]
}
},
"aggs": {
"interval": {
"date_histogram": {
"field": "report_date",
"interval": "year",
"min_doc_count": 0,
"extended_bounds": {
"min": "2021-01-01",
"max": "2021-12-31"
}
},
"aggs": {
"sales": {
"sum": {
"field": "attributed_sales_14d"
}
},
"spend": {
"sum": {
"field": "cost"
}
},
"clicks": {
"sum": {
"field": "clicks"
}
},
"impressions": {
"sum": {
"field": "impressions"
}
},
"roas": {
"bucket_script": {
"buckets_path": {
"sales": "sales",
"cost": "spend"
},
"script": "params.sales/params.cost",
"gap_policy": "insert_zeros"
}
}
}
},
"histogram_for_average": {
"date_histogram": {
"field": "report_date",
"interval": "day"
},
"aggs": {
"sales": {
"sum": {
"field": "attributed_sales_14d"
}
},
"spend": {
"sum": {
"field": "cost"
}
},
"roas": {
"bucket_script": {
"buckets_path": {
"sales": "sales",
"cost": "spend"
},
"script": "params.sales/params.cost",
"gap_policy": "insert_zeros"
}
}
}
},
"average_spend": {
"avg_bucket": {
"buckets_path": "histogram_for_average>spend"
}
},
"average_sales": {
"avg_bucket": {
"buckets_path": "histogram_for_average>sales"
}
},
"average_roas": {
"avg_bucket": {
"buckets_path": "histogram_for_average>roas"
}
}
}
}
```

This first histogram is used to represent data and can be done with `year`

, `day`

and `month`

values. The average that needs to be calculated must always be done with a `day`

interval. What I want to achieve is to replace the `average_roas`

with a calculation `average_sales/average_spend`

.

To further elaborate on the average_histogram. I have multiple documents for each day and the `sales`

for one day is the sum of all the documents for that individual day. That's why I want to sum them daily and then get the daily average sales. I do the same for `spend`

as well.